본문 바로가기
728x90

IT/SQL, 쿼리문공유43

[SQL] 순위 매기는 함수 row_number (feat. 검색어 순위 추출) 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, AWS QuickSight) 브랜드별 일별 매출 쿼리문 (feat. 날짜 필터) 퀵사이트에서 (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, AWS 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.
[AWS Redshift 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) 전월 동일 시점 누적 데이터 비교 쿼리문(feat. 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 도 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) AS month ,COUNT(DISTINCT vi_i.. 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.
[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 (feat. 서브쿼리, 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] DATEDIFF 함수 _ 날짜 차이 구하기 DATEDIFF 함수는 두 날짜 또는 시간 표현식에서 날짜 부분의 차이점을 반환한다. 구문 DATEDIFF ( datepart, {date|time|timetz|timestamp}, {date|time|time|timestamp} ) 차이나는지 구하고 싶을 때 DATEDIFF ( day, 날짜, 날짜 ) 예 ) DATEDIFF ( day, '2021-01-01', '2021-01-02' ) -> 1일 차이나기 때문에 결과는 1 차이나는지 구하고 싶을 때 DATEDIFF ( week, '2021-01-01', '2021-12-31' ) -> 결과는 52 차이나는지 구하고 싶을 때 DATEDIFF ( qtr, '1998-07-01',.. 2022. 3. 8.
[AWS Redshift SQL] 숫자 세 자리 마다 콤마(,) 찍기 / 소수점 자릿수 맞추기 (feat. 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.
[AWS] Redshift 는 PostgreSQL을 기반으로 한다 Amazon Redshift는 PostgreSQL을 기반으로 한다. 따라서 MySQL 에서는 사용 가능한 함수고, 잘 돌아가는 쿼리문인데 Redshift 에서는 오류가 나는 경우들도 있다. 어떤 함수들의 경우 MySQL 에서 사용가능한데 Redshift 에서는 사용 불가능하다. 또, 아무리 Amazon Redshift 기반이 PostgreSQL 라고 해도 Amazon Redshift SQL 과 PostgreSQL 에는 차이가 있다. Amazon Redshift SQL 에서 모든 PostgreSQL 의 함수와 기능을 제공하는 것은 아니라는 것이다. 아래 공식문서에 자세히 나와있다. https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/c_redshift-and-po.. 2022. 3. 4.
[SQL예문] 신규주문 / 재주문 회원수 구하기 (feat. 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.
[MySQL] ALTER TABLE (ADD, DROP, RENAME,MODIFY) 컬럼추가 하기 (ALTER TABLE , ADD) student 테이블에 칼럼을 하나 추가해 보자 테이블에 `컬럼` 추가하려면 ALTER TABLE 테이블이름 ADD 컬럼이름 데이터타입 속성 ALTER TABLE student ADD gender CHAR(1) NULL; DESC student; #student 테이블 정보 보기 결과 화면 > 컬럼명 바꾸기 (ALTER TABLE , RENAME COLUMN TO) 컬럼명을 바꿔보자 student_number -> registration_number ALTER TABLE 테이블이름 RENAME COLUMN 기존 컬럼명 TO 새로운 컬럼명; ALTER TABLE student RENAME COLUMN student_number TO registration_.. 2022. 2. 3.
[MySQL] 테이블 컬럼 정보 보기 DESCRIBE (DESC) 테이블 컬럼 정보를 한 눈에 보여주는 DESCRIBE 이런식으로 해당 테이블의 컬럼 구조, 각 컬럼의 데이터 타입, 속성을 볼 수 있다. Field : 컬럼의 이름 Type : 컬럼의 데이터 타입 Null : 컬럼의 Null 속성 유무 Key : Primary Key, Unique 속성 여부 Default : 컬럼의 기본값 Extra : AUTO_INCREMENT 등의 기타 속성 DESCRIBE를 DESC라고 줄여서 써도 된다. 2022. 2. 3.
728x90

티스토리툴바