MySQL 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. [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] 월별 주문, 방문, 가입, 신규주문/ 재주문 쿼리문 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] 월별 방문자수(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. [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예문] 신규주문 / 재주문 회원수 구하기 (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. [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] 테이블 컬럼 값 생성, 수정, 삭제 (CREATE, UPDATE, DELETE) animal_info 라는 테이블 생성하기 id : Primary Key로 사용할 컬럼, 데이터 타입 : INT AUTO_INCREMENT 속성 주기 type : 동물 종류(사자, 코끼리 등), 데이터 타입 : VARCHAR, 최대 길이 : 30자 name : 동물 이름(리오, 조이 등), 데이터 타입 : VARCHAR, 최대 길이 : 10자 age : 나이, 데이터 타입 : TINYINT sex : 성별, 데이터 타입 : CHAR, 최대 길이 : 1자 weight : 몸무게, 데이터 타입 : DOUBLE feature : 특징 묘사, 데이터 타입 : VARCHAR, 최대 길이 : 500자 entry_date : 동물원에 들어온 날짜, 데이터 타입 : DATE feature .. 2022. 2. 3. [MySQL] Join (LEFT, RIGHT, INNER) / UNION LEFT OUTER JOIN (= LEFT JOIN) 왼쪽 테이블 기준으로 오른쪽 테이블을 합침 SELECT i.id, i.name, s.item_id, s.inventory_count FROM item AS i LEFT OUTER JOIN stock AS s #테이블에 alias 붙여줌 ON i.id = s.item_id #테이블 조인 조건 조인할 때는 SQL 문이 길어지기 때문에 테이블 이름에 alias를 붙여주는 게 좋다. RIGHT OUTER JOIN (= RIGHT JOIN) 오른쪽 테이블 기준으로 왼쪽 테이블을 합침 SELECT i.id, i.name, s.item_id, s.inventory_count FROM item AS i RIGHT OUTER JOIN stock AS s #테이블에 a.. 2022. 2. 1. [MySQL] 뷰 (VIEW) 만들기 뷰 : 조인 등의 작업을 해서 만든 '결과 테이블' 이 가상으로 저장된 형태 일시적으로 생성한 결과 테이블을 저장해둔 것 뷰(VIEW)는 가상테이블이라고도 한다. #AS 뒤에 나오는 쿼리문의 결과 테이블을 VIEW 로 저장하라 CREATE VIEW three_tables_joined AS SELECT i.id, i.name, AVG(star) AS avg_star, COUNT(*) AS count_star FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id LEFT OUTER JOIN member AS m ON r.mem_id = m.id WHERE m.gender = 'f' GROUP BY i.id, i.name HAVING COUNT(*) .. 2022. 2. 1. [MySQL] 서브쿼리 사용법 서브쿼리 : SQL 문 안에 부품처럼 들어가는 SELECT 문 sub (하위의, 일부분의) Query (데이터베이스에 보내는 요청) 서브쿼리를 쓸 땐 괄호로 감싸줘야함 서브쿼리는 HAVING 절, SELECT 절, WHERE 절, FROM 절 등에서 사용할 수 있다. HAVING 절에 있는 서브쿼리 예시 쿼리문 > SELECT i.id, i.name, AVG(star) AS avg_star FROM item AS i LEFT OUTER JOIN review AS r ON r.item_id = i.id GROUP BY i.id, i.name HAVING avg_star 서브쿼리를 포함하는 전.. 2022. 2. 1. [MySQL] GROUP BY , HAVING, WITH ROLLUP GROUP BY 칼럼들을 어떠한 조건의 그룹으로 묶어서 그룹별 집계를 보고 싶을 땐 GROUP BY를 사용 한다. GROUP BY 로 그루핑 해주고 집계함수를 사용하면 그루핑 된 각 그룹에 대해서 집계함수 들이 각각 실행된다. (GROUP BY 를 쓰지 않았을 때는 테이블 전체 row 가 하나의 그룹인 것) 예시 쿼리문 > SELECT gender, COUNT (*) AS COUNT, #COUNT (*) 는 조회 되는 row 의 개수를 구해주는 표현식 AVG (height), MIN(weight) FROM member GROUP BY gender ; 출력 결과 > gender COUNT AVG (height) MIN(weight) male 987 179 55.8 female 1523 165 41.3 예시.. 2022. 1. 26. [MySQL] 컬럼 이름 바꿔서 보여주기 Alias (AS) 컬럼 이름 바꿔서 보여주는 것을 Alias (별명) 라고 한다. 컬럼명이 height 인데 보여줄 때 '키' 라는 단어로 보여주고 싶다면 AS 를 사용해서 명명 해주면 된다. AS 로 컬럼 Alias(별명) 붙여주기 SELECT email, height AS 키, weight AS 몸무게, weight / (height/100) * (height/100) AS BMI FROM member; (참고 : 칼럼끼리 계산은 + 더하기 - 빼기 * 곱하기 / 나누기 % 나머지 구하기 이렇게 가능) Alias 에 공백이 포함되면 작은 따옴표로 감싸줘야한다. SELECT email, height AS 키, weight AS 몸무게, weight / (height/100) * (height/100) AS 'BMI 지수.. 2022. 1. 26. [MySQL] NULL 이 있는 로우 조회하기 NULL 은 값이 없음을 나타내는 단어다. 데이터 테이블에 값이 없는 로우가 있을 수 있다. 회원 가입할 때 선택 값으로 넣어둔 항목들은 유저가 회원 가입시 빈 값으로 두고 넘어 갈 수 있기 때문에 그 부분은 NULL 이 된다. 데이터 추출 시 NULL 인 부분들을 조회 하고 싶을 때 어떻게 조회 하는지 알아보자 IS NULL NULL 인 로우 조회 # member 테이블에서 address 칼럼이 NULL 로우들 조회 SELECT * FROM member WHERE address IS NULL; IS NOT NULL NULL 이 아닌 로우 조회 # member 테이블에서 address 칼럼이 NULL 이 아닌, 값이 있는 로우들만 조회 SELECT * FROM member WHERE address IS .. 2022. 1. 26. [MySQL] DATE 관련 SQL 함수 모음 (CURDATE, DATEDIFF 등) DATE 데이터 타입을 갖는 컬럼의 값들을 다룰 때 자주 쓰는 함수들 모음 1. 연도, 월, 일 추출 연도 : YEAR ( ) 월 : MONTH ( ) 일 : DAYOFMONTH ( ) # member 테이블에 birthday 날짜의 연도가 1998인 데이터 출력 SELECT * FROM member WHERE YEAR(birthday) = '1998'; # member 테이블에 birthday 날짜의 월이 6,7,8인 데이터 출력 SELECT * FROM member WHERE MONTH(birthday) IN (6,7,8); # member 테이블에 birthday 날짜의 일이 15일~17일인 데이터 출력 SELECT * FROM member WHERE DAYOFMONTH(birthday) BETWE.. 2022. 1. 23. [MySQL] 대소문자 구분해서 검색하기 _ BINARY 데이터베이스의 테이블에 적용된 기본 설정 중, 문자열이 동일한지 확인할 때, 대소문자를 구별하지 않겠다고 설정 되어있는 테이블들이 있다. 이런 경우 대소문자가 달라도 알파벳만 같으면 같다고 판단이 되어버린다. 이 설정에 상관없이 대소문자를 구분해서 검색하는 방법이 있을까? 바로 BINARY 를 사용하면 가능하다. LIKE BINARY '%g%' SELECT * FROM member WHERE sentence LIKE BINARY '%g%'; #member 테이블에 sentence 칼럼에 소문자 g가 포함된 데이터 출력 SELECT * FROM member WHERE sentence LIKE BINARY '%T%'; #member 테이블에 sentence 칼럼에 대문자 T가 포함된 데이터 출력 BINARY.. 2022. 1. 23. [MySQL] % ' " _ 문자로 쓰고 싶을 땐 이스케이핑 원래 특정 의미('임의의 길이를 가진 문자열')를 나타내던 문자(%)를 그 특정 의미가 아니라, 일반적인 문자처럼 사용하고 싶을 땐 이스케이핑(escaping) 을 해야한다. 어떤 문자가 그것에 부여된 특정한 의미, 기능으로 해석되는 게 아니라 그냥 단순한 문자 하나로 해석되도록 하는 것을 이스케이핑이라고 한다. % 는 'LIKE에서 쓰이는 표현식'으로 , ‘임의의 길이를 가진 문자열’을 의미하는데 그냥 문자로 쓰고 싶다면 % 앞에 역슬래쉬(백슬래쉬, backslash) 기호를 붙여주면 된다. 백슬래쉬는 키보드상에 엔터키 위에 원화 표시(\) 되어있는 키다. SELECT * FROM member WHERE sentence LIKE '%\%%' #member 테이블에 sentence 칼럼에 문자 % 가 포.. 2022. 1. 23. [MySQL] 쿼리문 분석하기 예제 이 쿼리문으로 대체 어떤 데이터를 얻을 수 있을지! 스스로 분석해 보실 수 있도록 예제를 준비했습니다 아래 쿼리문을 보시고 어떤 데이터를 얻을 수 있을지 한번 생각해보세요! 답은 아래에 있습니다 ----------------------------------------------------------------------------------------------------------------------------------- SELECT KEYWORD ,SUM(QC) AS QC // QC : 쿼리(키워드) 검색량을 나타내는 칼럼 ,SUM(CLK_CNT) AS CC // CLK_CNT : 쿼리 검색 결과들 중 무엇이라도 클릭한 횟수를 나타내는 칼럼 ,(SUM(CLK_CNT)/SUM(QC)) AS CTR .. 2019. 8. 22. [MySQL] DB에서 원하는 조건의 데이터 추출하기 안녕하세요 요즘 기업들에서 꼭 개발팀이 아니더라도 DB에 접속해서 필요한 데이터들 추출하여 사용하는 경우가 많더라구요. 해당 테이블 권한만 받아서요. 성과 데이터가 필요하기도 하고, 앞으로 추진할 프로젝트를 위해 데이터 분석도 해봐야 하고 이유는 다양해요. 그래서 실생활에(?) 자주 쓰이는 간단한 쿼리문 공유드려요 :) ★오늘 포스팅할 내용은 요것입니다★ -------------------------------------------------------------------------- DB에는 너무나 많은 데이터가 즐비해있다! 그 중에 내가 원하는 조건의 데이터만 깔끔하게 추출하고싶다! 그것도 간단하고 짧은 쿼리문으로! ----------------------------------------------.. 2019. 8. 22. 이전 1 다음 728x90 반응형 LIST
