본문 바로가기

SQL

sql ) 해당 브랜드의 주문당 평균 객단가 구하는 방법 쿼리문 해당 브랜드의 주문당 평균 객단가 구하는 방법 ex) 한 주문건에 a ~ z 까지 여러가지 브랜드 제품들이 다양하게 랜덤으로 포함되어 있는데 그 중 c 브랜드가 포함된 주문건들 중에 c브랜드만의 매출이 얼마인지 즉, 주문당 c 브랜드의 평균 객단가가 얼마인지 구하는 방법! SELECT left(od.od_receipt_time, 7) as month -- 결제날짜 2024-01-01 이런 형식에서 앞에 7문자만 가져오면 month ,sum(it.it_price)/count(od.od_id) as AOV -- Average Order Value 객단가 -- 해당 브랜드 구매금액 total 나누기 주문수 FROM "shop_order" AS od JOIN "shop_cart" AS ct ON od.od_id.. 2024. 3. 25.
SQL 데이터 조회 필수 기초 명령어 SQL 데이터 조회 필수 기초 명령어 데이터를 조회할 목적으로 SQL 을 배우려는 입문자 분들이 알아야 할 필수 기초 명령어와 예문들입니다 SELECT : 데이터를 조회할 열을 선택합니다. FROM : 조회할 데이터가 있는 테이블을 지정합니다. WHERE : 특정 조건을 만족하는 행을 조회합니다. GROUP BY : 결과를 특정 열의 값에 따라 그룹화합니다. ORDER BY : 결과를 특정 열의 값에 따라 정렬합니다. SELECT column1, column2 FROM table_name WHERE condition GROUP BY column1 ORDER BY column2 DESC; 퀴리문 1) 예를 들어, "주문" 테이블에서 고객별로 주문한 상품의 수를 세고, 상품 수에 따라 내림차순으로 정렬된 결.. 2024. 2. 28.
PostgreSQL case when 활용, 신규주문 재주문 구분해주기 - 레드시프트 PostgreSQL case when 활용, 신규주문 재주문 구분해주기 - 레드시프트 select * , (case when o_rn = 1 then 1 else 0 end) as new_order -- 해당날짜에 o_rn 이 1이면 1 반환 , (case when o_rn = 1 then 0 else 1 end) as re_order -- 해당날짜에 o_rn 이 1이 아니면 1 반환 from (서브쿼리 테이블) select * , (case when o_rn = 1 then 1 else 0 end) as new_order -- 해당날짜에 o_rn 이 1이면 1 반환 , (case when o_rn = 1 then 0 else 1 end) as re_order -- 해당날짜에 o_rn 이 1이 아니면 1.. 2024. 1. 24.
[SQL] 순위 매기는 함수 row_number (ft. 검색어 순위 추출) SQL 순위 매기는 함수 row_number row_number 함수는 1 부터 순위 중복 없이 쭉 매겨주는 함수 동점이 있어도 같은 순위로 매기지 않음 예시를 통해 사용법을 알아보자 최근 1년 검색어 순위를 추출해보자 결과는 아래 표와 같이 나오면 좋겠다 키워드별 검색량에 따라 내림차순 정렬이 되어있고 맨 위에서부터 차례로 1, 2, 3, .... 순위를 매겨놨다. 쿼리문 > select (row_number() over()) ,lower ,query from (SELECT lower(pp_word) -- 대소문자 구분없이 키워드 추출 ,count(*) as query -- 검색량 FROM keyword_table where pp_date between current_date - 365 and curr.. 2022. 12. 20.
[SQL, QuickSight] 브랜드별 일별 매출 쿼리문 (ft. 날짜 필터) 퀵사이트에서 (Amazon QuickSight) 브랜드 판매순위 표 만드는 방법 퀵사이트 기본 사용 방법은 이 곳에서 👉 1. 데이터세트 생성하기 👉 2. 분석, 대시보드 만들기 브랜드별 지정한 기간동안 (필터) 매출 표 ㄴ 그룹화 기준을 "브랜드" 컬럼으로 지정하고 값에 주문수, 주문금액 지정 브랜드별 매출 순위 차트 ㄴ 위에서 만든 표를 복사해서 차트 형식만 오른쪽 차트 처럼 선택하면 자동 생성 됨 날짜, 브랜드 필터로 두 차트 연결하기 ㄴ 날짜 필터를 두 차트에 걸리게 선택, 시트에 날짜 필터 추가 하기 SELECT left(od.od_receipt_time, 10) AS "date", it.it_brand, SUM(ct.ct_qty)AS "od_qty" , SUM(ct.ct_price * ct.c.. 2022. 9. 23.
[SQL, QuickSight] 일별 가입수, 가입경로 쿼리문/ 차트 퀵사이트에서 (AWS QuickSight) 차트 만드는 방법 퀵사이트 기본 사용 방법은 이 곳에서 👉 1. 데이터세트 생성하기 👉 2. 분석, 대시보드 만들기 상위 가입경로 일별 추이 차트 ㄴ x축은 "날짜" 선택, 값 필드엔 "가입수" 선택, 색상엔 "가입경로" 선택 지정한 날짜에 가입경로별 가입수 ㄴ 그룹화 기준은 "가입경로", 값은 "가입수" 날짜 필터 만들어서 시트에 추가 일별 가입수 추이 차트 ㄴ x축은 "날짜" 선택, 값은 "가입수" 선택 SELECT left(get_datetime,10) as "date" , how_get , count(*) as join_cnt FROM s2_s2_member_get group by "date", how_get 2022. 9. 23.
[SQL] 월별 주문, 방문, 가입, 신규주문/ 재주문 쿼리문 1. 월별 주문수, 주문금액 SELECT SUBSTRING(od_receipt_time,1,7) AS od_month , COUNT(SUBSTRING(od_receipt_time,1,7)) AS gl_od_cnt , SUM(od_receipt_price) AS "gl_od_amount($)" , SUM(od_receipt_price) * (SELECT data FROM s2_s2_api_currency_cms WHERE currency = 'USD' ORDER BY db_date desc limit 1) AS "gl_od_amount(krw)" FROM "dev"."gl_test"."s2_g5_shop_order" WHERE SUBSTRING(od_receipt_time,1,10) BETWEEN '202.. 2022. 9. 20.
[SQL, QuickSight] 누적일 비교 쿼리문 (전 월 누적일, 전 년 동 월 누적일) AWS QuickSight 에서 데이터 시각화 하기 위해 작성한 상세 쿼리문 공유합니다. 테이블명, 컬럼명은 DB 마다 다르겠지만 어떻게 쿼리문이 작성 되는지 살펴보시면 도움 되실거에요 퀵사이트 기본 사용 방법은 이 곳에서 👉 1. 데이터세트 생성하기 👉 2. 분석, 대시보드 만들기 1. 전 월 누적일 비교 표 (전 월 동기간) - MTD 와 전 월 동일 기간 비교 SELECT visit.month , gl_visit , gl_order , gl_total_price , gl_total_price * (SELECT data FROM "s2_s2_api_currency_cms" WHERE currency = 'USD' ORDER BY db_date desc limit 1) AS "gl_total_price.. 2022. 9. 20.
[SQL] 문자열 더해서 출력하는 방법 (concat or ||) 2가지 방법이 있다 1. || 기호를 쓰거나 (키보드상에 원화 기호랑 같은 키패드에 있는 | 짝대기 기호 두 번) 2. concat 함수를 쓰거나 select s2_product_code as id , it_explan2 as description , it_cust_price as price , it_price as sale_price , it_img1 as img_link from item_table 위와 같은 쿼리문의 결과로 price 컬럼과 sale_price 컬럼이 그냥 숫자만 나오는 상황에 숫자 뒤에 'USD' 라는 통화 정보를 붙여서 출력하고 싶다면 아래와 같이 쿼리문을 작성해주면 된다 select s2_produ.. 2022. 8. 30.
[SQL] 전월 동일 시점 누적 데이터 비교 쿼리문(ft. Line Chart) 테이블명과 컬럼명은 회사마다 다릅니다. SQL 도 DBMS 에 따라 조금씩 표현 방법이 다릅니다. 어떤식으로 쿼리문을 작성하면 되는지 이해하며 보시고 상황에 맞게 수정해서 사용해보세요 :) 전월 동일 시점 누적 데이터 비교 = 전월 같은 날 까지의 누적 데이터 비교 예를들어 오늘이 22.06.13 이고, 22.06.01 ~ 22.06.12 사이의 누적 주문 데이터와 22.05.01 ~ 22.05.12 사이의 누적 주문 데이터를 비교해 보고 싶을 때 , (아직 오늘 데이터는 변하는 중이니까 어제까지의 데이터로 비교해보겠다) 즉 같은말로, 이번달 1일부터 어제까지의 누적주문 금액과 전월 1일부터 전월 동일 까지의 누적 금액을 비교해 보고 싶을 때 , 아래와 같은 쿼리문을 작성해주면 비교해 볼 수 있다. SE.. 2022. 6. 13.
[SQL] 월별 방문자수(UV), 월별 가입수 쿼리문 [SQL] 월별 방문자수(UV), 월별 가입수 쿼리문 테이블명과 컬럼명은 회사마다 다릅니다. SQL 도 DBMS 에 따라 조금씩 표현 방법이 다릅니다. 어떤식으로 쿼리문을 작성하면 되는지 이해하며 보시고 상황에 맞게 수정해서 사용해보세요 :) 월별방문자수 (UV - UniqueVisit) SELECT LEFT(vi_date,7) AS month ,COUNT(DISTINCT vi_ip) AS uv FROM visit WHERE vi_date BETWEEN '2021-01-01' AND 'current_date' AND LEFT(vi_ip,9) !='11.11.100' -- 회사 IP 제외 GROUP BY LEFT(vi_date,7) ORDER BY month ASC SELECT LEFT(vi_date,7).. 2022. 5. 27.
[SQL] 상품등록 후 최초 30일 판매 데이터 쿼리문 테이블명과 컬럼명은 회사마다 다릅니다. SQL 도 DBMS 에 따라 조금씩 표현 방법이 다릅니다. 어떤식으로 쿼리문을 작성하면 되는지 이해하며 보시고 상황에 맞게 수정해서 사용해보세요 :) 상품등록 후 최초 30일 판매 데이터 SELECT it.s2_product_code AS SKU ,it.it_name AS product_name ,it_time AS register_date --,DATEDIFF(day, it_time, current_date) AS after_reg_date -- 상품등록한지 몇일 지났는지 ,SUM(ct.ct_qty) AS first_30days_od_qty ,SUM(ct.ct_price * ct.ct_qty) AS first_30days_od_price FROM shop_orde.. 2022. 5. 27.
언어별 주석 처리 방법 (C, JAVA, CSS, HTML, SQL, Python 등) 언어별 주석 처리 기호 모음 ! C, C++, C#, Objective-C ,JAVA, Javascript, R, Go, Kotlin, Swift 의 주석 처리 한 줄 주석은 // 여러 줄 주석은 /* */ // 한 줄 주석 /* 여러 줄 주석 */ HTML , XML 의 주석처리 한 줄도, 여러 줄도 CSS 의 주석처리 CSS 주석은 /* */ /* 영화 제목 */ .movie h1 { color: #4d9fff; text-align: center; font-size: 40px; } /* 영화 줄거리 */ .movie p { font-size: 20px; } Python, Ruby 의 주석처리 파이썬의 경우에 한줄 주석은 # 여러줄 주석은 작은따옴표 3개씩 ''' ''' or 큰 따옴표 3개씩 """ .. 2022. 4. 28.
[SQL 예문] SQL 연습하기 3 (기초 쿼리문) 1) '회원 가입 경로' 테이블에 있는 모든 컬럼을 출력해보자 테이블명 : member_get SELECT 는 컬럼을 선택하는 명령어인데 * 를 뒤에쓰면 모든 컬럼 선택한다는 의미 SELECT * FROM member_get idx, mb_id, how_get, get_datetime 이렇게 4개 컬럼이 존재한다. how_get 컬럼이 가입경로 컬럼인데 빈칸은 고객이 가입경로 체크 안한 것 ㅎㅎ 2) how_get 컬럼이 빈칸인건 제외하고 값이 있는 것만 출력해보자 SELECT * FROM member_get WHERE how_get != '' WHERE 는 조건을 걸어주는 명령어 = 이 같다는 의미고 != 는 같지 않다는 의미다 작은 따옴표를 두번 써주면 빈칸을 의미. 즉 how_get 컬럼이 빈칸이 .. 2022. 3. 24.
[SQL 예문] SQL 연습하기 2 (기초 쿼리문) 1) member 테이블에 있는 모든 컬럼을 출력해보자 SELECT 는 컬럼을 선택하는 명령어인데 * 를 뒤에쓰면 모든 컬럼 선택한다는 의미 SELECT * FROM member 컬럼이 대략 20개 정도 있는 테이블이다. id, name, nick_name, 가입일(mb_datetime), email 기타등등 2) 가입일이 '2021-01-01' ~ '2021-01-10' 인 데이터들만 출력해보자 SELECT * FROM member WHERE mb_datetime BETWEEN '2021-01-01 00:00:00' AND '2021-01-10 23:59:59' WHERE 조건절에서 가입일 컬럼 mb_datetime 를 해당 기간으로 걸어준다 3) 가입일이 '2021-01-01' ~ '2021-01-1.. 2022. 3. 24.
[SQL 예문] SQL 연습하기 1 (기초 쿼리문) 입문용 예시 쿼리문 :) 1) visit_sum 테이블에 있는 모든 컬럼을 출력해보자 SELECT 는 컬럼을 선택하는 명령어인데 * 를 뒤에쓰면 모든 컬럼 선택한다는 의미 SELECT * FROM visit_sum 컬럼이 vs_date 와 vs_count 두개 존재하는 테이블 . vs_date 는 날짜이고 vs_count 는 방문자 count 한 숫자. 2) visit_sum 테이블에서 날짜조건을 걸어보자. - WHERE 2021-01-01 부터 2021-01-31 기간동안 일별 방문자수를 출력해보자 SELECT * FROM visit_sum WHERE vs_date BETWEEN '2021-01-01' AND '2021-01-31' 날짜가 정렬이 안되어 있다. 3) 날짜를 오름차순으로 정렬해보자 - O.. 2022. 3. 24.
[SQL 예문] 상품등록 후 최초 30일간 판매 데이터 (반응 좋은 신제품 알아보기) 잘나가는 신제품, 반응이 좋은 신제품을 알아보고 싶을 때 각 SKU 별 상품등록 후 최초 30일간의 판매 데이터를 보고 파악할 수 있다. 아래 캡쳐본 같은 형식의 데이터를 뽑아보기 위해 쿼리문을 작성했다. 쿼리문 ) SELECT it.s2_product_code AS SKU ,it.it_name AS product_name ,it_time AS reg_date ,TO_CHAR(SUM(ct.ct_qty) , '999,999,999') AS first_30days_od_qty ,TO_CHAR(SUM(ct.ct_price * ct.ct_qty), '999,999,999.99' ) AS first_30days_od_price FROM shop_order AS od JOIN shop_cart AS ct ON od.. 2022. 3. 21.
[SQL 예문] 브랜드별 매출 국가 순위 TOP 10 (ft. 서브쿼리, Round_number() Over () ) 브랜드별, 어느 국가에서 잘 팔리는지 매출 순으로 상위 국가 10개씩 추출하기 아래와 표와 같은 형식으로 데이터를 뽑아보자 JOIN 2번 하고 서브쿼리 쓰고 ROW_NUMBER() OVER (PARTITION BY _ ORDER BY _) 함수를 사용했다. SELECT it_brand ,od_b_country ,od_price FROM ( SELECT it.it_brand ,od.od_b_country ,TO_CHAR (SUM(ct.ct_qty) , '999,999,999') AS "od_qty" ,TO_CHAR (SUM(ct.ct_price * ct.ct_qty), '999,999,999.99' ) AS "od_price" ,ROW_NUMBER() OVER (PARTITION BY it_brand OR.. 2022. 3. 18.
[SQL 예문] 브랜드별 판매수량, 금액 구하기 (feat. JOIN) 우리 회사 DB 구조 상, 브랜드별 판매수량, 금액을 추출하기 위해서는 세 테이블을 조인 해야한다. ITEM 테이블 , CART 테이블, ORDER 테이블 왜 이렇게 세개가 필요하냐면, CART 와 ORDER 테이블엔 BRAND 명 칼럼이 없음 ORDER 테이블엔 주문번호별 총주문금액은 있어도 한 주문번호 안에 어떤 상품들을 담았는지는 없음 그건 CART테이블에 데이터 존재 ITEM 테이블에 BRAND 명 칼럼 존재. CART 테이블에 상품별 판매수량, 금액 칼럼 존재 ORDER 테이블에 주문번호 (od_id), 결제일시 (od_receipt_time) 칼럼 존재 우선 ORDER 테이블과 CART 테이블을 주문번호 (od_id) 를 key 로 해서 JOIN 한다. 이렇게 JOIN 한 결과와 ITEM 테이.. 2022. 3. 18.
[SQL 함수] RANK() OVER (Partition by _ Order by _ ) 그룹핑 한 후 순위를 매겨주는 함수 SELECT RANK( ) OVER ( PARTITION BY 그룹핑할 칼럼 ORDER BY 정렬할 칼럼 ) FROM 테이블명 ( ORDER BY 정렬할 칼럼 DESC or ASC or 생략하면 ASC가 디폴트값 ) 이 긴 함수가 한 세트~ PARTITION BY 에 지정한 칼럼 기준으로 그룹핑 해주고 ORDER BY 에 지정한 칼럼 기준으로 정렬해준담에 RANK 행 마다 순위를 매겨준다 ROW_NUMBER() OVER (Partition by _ Order by _) 와의 차이점은 ROW_NUMBER( ) : 1등이 2명이어도 1등, 2등으로 나눔 RANK( ) : 1등이 2명이면 그 다음 순위는 3등으로 매김 예시 쿼리문 ) SELECT s.ip, s.session.. 2022. 3. 14.
[SQL 함수] ROW_NUMBER() OVER (Partition by _ Order by _ ) 그룹핑 한 후 순번을 매겨주는 함수 SELECT ROW_NUMBER( ) OVER ( PARTITION BY 그룹핑할 칼럼 ORDER BY 정렬할 칼럼 ) FROM 테이블명 ( ORDER BY 정렬할 칼럼 DESC or ASC or 생략하면 ASC가 디폴트값 ) 이 긴 함수가 한 세트~ PARTITION BY 에 지정한 칼럼 기준으로 그룹핑 해주고 ORDER BY 에 지정한 칼럼 기준으로 정렬해준담에 ROW_NUMBER 행 마다 순서를 매겨준다 예를 들어 아래 왼쪽 테이블을 오른쪽 테이블 같이 그룹핑, 정렬 해주고 싶으면 이렇게 함수를 작성하면 된다. ROW_NUMBER( ) OVER (PARTITION BY '멤버 아이디' ORDER BY '주문한 날짜') 실무에 사용되는 쿼리문을 통해 한번 더 확인해.. 2022. 3. 11.
[AWS Redshift SQL] 숫자 세 자리 마다 콤마(,) 찍기 / 소수점 자릿수 맞추기 (ft. TO_CHAR) AWS Redshift 에서 123,444,000 처럼 숫자 세자리 수 마다 콤마 찍어주는 방법 MySQL 에서는 FORMAT 함수로 되지만 Redshift 에서는 FORMAT 함수 지원 안해줌.. TO_CHAR 함수 사용하면 할 수 있다. SELECT TO_CHAR ( 컬럼명 , '999,999,999,999' ) FROM 테이블명 999,999 는 출력 될 예상 수치 보다 큰 숫자로 입력해야 잘 적용 됨으로 일단 큰 숫자로 설정하는게 좋다 ex) SELECT TO_CHAR ( 주문금액, '999,999,999,999,999' ) FROM shop_order 소수점 자리도 몇자리까지만 출력되게 할 .. 2022. 3. 7.
[SQL예문] 신규주문 / 재주문 회원수 구하기 (ft. CASE WHEN , 서브쿼리) 주문 테이블에서 일정 기간 동안 신규주문 회원수와 재주문 회원수를 구하고 싶다. 신규주문과 재주문 구분은 해당 아이디의 주문 횟수가 1이면 신규주문 해당 아이디의 주문 횟수가 1초과 이면 재주문 으로 구분한다. CASE WHEN 구문 CASE WHEN COUNT(mb_id) = 1 THEN '신규주문' WHEN COUNT(mb_id) > 1 THEN '재주문' ELSE '-' END 이 CASE WHEN 구문을 SELECT 문에 하나의 칼럼으로 넣을 것이다. 우선 회원아이디로 group by 해서 회원별 주문횟수, 주문총금액을 구한다. IN ( ) 함수 사용법은 1 THEN '재주문' ELSE '-' END) AS 주문유형 FROM shop_order WHERE od_time BETWEEN '2022-0.. 2022. 3. 3.
[SQL예문] 월별 / 일별 주문 수 추출하기 (feat. SUBSTRING) 월별 주문 수 추출하기 우선, 주문관련 데이터가 누적되는 shop_order 테이블에 어떤 컬럼들이 있는지 살펴본다. SELECT * FROM 주문테이블 여러 컬럼들 중 주문번호, 주문날짜 및 시간, 주문 상태 이 세 칼럼이 필요할 것 같다. 월별 주문 수 추출 쿼리문 SUBSTRING 함수 사용법👀 월별 주문수 구하기 위해 월별로 GROUP BY 가 필요! 날짜 및 시간 컬럼에서 yy-mm 까지만 잘라서 사용! (SUBSTRING 함수로) SUBSTRING (od_receipt_time,1,7) AS "YY-MM" ㄴ od_receipt_time 컬럼에서 1번째 문자열부터 7개까지만 사용하겠다. 이 컬럼명은 yy-mm 으로 명명 한다. COUNT(od_id) AS od_count ㄴ od_id 주문번호.. 2022. 2. 25.
728x90
반응형
LIST

티스토리툴바