본문 바로가기

SQL71

[BigQuery] QUALIFY 구문 🎯 QUALIFY일반적으로 윈도우 함수와 함께 사용됨특정 조건을 충족하는 행을 선택하거나 제거하는 데 사용됨윈도우 함수의 결과를 기반으로 행을 필터링 하는데 유용함 예시 쿼리(1) : with 절과 where 사용order_id(주문 아이디), amount(주문 금액)으로 이루어진 테이블(orders)에서 주문 아이디별로 가장 높은 가격의 물건만 보고 싶은 경우, 다음과 같이 with 절을 이용해 서브쿼리로 결과를 출력할 수 있다.where 절로 조건을 거는 경우WITH max_amount_per_order AS ( SELECT order_id, amount, MAX(amount) OVER (PARTITION BY order_id) AS max_amount FROM orders.. 2024. 5. 20.
[Programmers] 상품을 구매한 회원 비율 구하기 문제 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율 👉 (=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력하는 SQL문을 작성 - 상품을 구매한 회원의 비율은 소수점 두번째자리에서 반올림하고, - 전체 결과는 년을 기준으로 오름차순 정렬해주시고 년이 같다면 월을 기준으로 오름차순 정렬할 것! https://school.programmers.co.kr/learn/courses/30/lessons/131534 프로그래머스 코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요. programmers.co.kr 풀.. 2024. 4. 9.
[Programmers] 특정 기간동안 대여 가능한 자동차 대여 비용(풀이) 문제 https://school.programmers.co.kr/learn/courses/30/lessons/157339 프로그래머스 코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요. programmers.co.kr 풀이 📍 조건 - 자동차 종류가 '세단' 또는 'SUV'인 자동차 중 - 2022년 11월 1일부터 2022년 11월 30일까지 대여 가능하고 - 30일간의 대여 금액이 50만 원 이상 200만 원 미만인 자동차에 대해서 - 자동차 ID, 자동차 종류, 대여 금액(칼럼명: FEE) 리스트 출력 - 대여 금액 내림차순, 자동차 종류 오름차순 정렬, 자동차 ID를 기준으로 내림차순 📍 1차 조.. 2024. 4. 9.
[BigQuery] 데이터 타입별 함수 정리 (문자열과 시간/날짜) 🎯 데이터를 그대로 가져오지 않고 변환이 필요한 경우에 쓸 수 있는 함수를 알아보자 SELECT 문에서 데이터를 변환시킬 수 있으며, WHERE의 조건문에서도 사용 가능 데이터의 타입에 따라 다양한 함수가 존재 1. 자료 타입을 변형하는 함수 : CAST SELECT CAST(1 AS STRING) # 숫자 1을 문자 1로 변경 👉 더 안전하게 데이터 타입 변경하기 : SAFE_CAST() → 변환이 실패할 경우 NULL을 반환하며, 다음과 같은 경우에 에러 대신 NULL이 됨 SELECT SAFE_CAST("문자열" AS INT64) (유사 사례) 안전하게 나누기 연산하기 : SAFE_DIVIDE() SAFE_DIVIDE(x, y) # zero error 대신 null 발생 2. 문자열을 다루는 함수 .. 2024. 4. 1.
[PostgreSQL] 10. 지속률과 정착률 한 번에 산출하는 쿼리(종합) 참고 지속률과 정착률은 모두 등록일 기준으로 n일 후의 행동을 집계하는 것이다. 따라서 등록일로부터 n일 경과하지 않은 상태라면 집계가 불가능하다.(1일 ~ 7일과 같은 단기간 지표를 활용하는 이유) 정착률은 7일 동안의 기간을 집계하므로, 실제로 며칠 사용했는지는 알 수 없다. 종합 쿼리 정착률을 계산하기 위해 만들었던 repeat_interval 테이블의 형식을 수정하면 지속률까지 계산할 수 있다. WITH repeat_interval(index_name, interval_begin_date, interva_end_date) AS ( VALUES ('01 day repeat ', 1, 1) , ('02 day repeat ', 2, 2) , ('03 day repeat ', 3, 3) , ('04 d.. 2024. 2. 4.
[PostgreSQL] 9. 매일의 N일 정착률 추이 확인하기(종합쿼리 포함) N일 지속률과 N일 정착률의 추이 n일 지속률과 n일 정착률을 따로 집계하면 등록 후 며칠간 사용자가 안정적으로 서비스를 사용하는지, 며칠 후에 그만두는 사용자가 많아지는지 등을 알 수 있다. 만약 지속률이나 정착률이 극단적으로 떨어지는 시점이 있다면, 해당 시점을 기준으로 공지사항 등을 전달하거나 N일 이상 사용한 사용자에게 보너스를 주는 등의 대책을 수행할 수 있을 것이다 ✨ 이번 포스팅에서는 지속률에 이어 정착률을 확인할 수 있는 종합 쿼리를 작성해본다 매일의 N일 정착률 추이 👉 지속률과 마찬가지로, 대책이 의도한 대로의 효과가 있는지 확인하려면 정착률을 매일 집계한 리포트가 필요하다 👉 참고로 7일 정착률이 극단적으로 낮은 경우에는 정착률이 아니라 '다음날 지속률' ~ '7일 지속률'을 확인해서.. 2024. 2. 4.
[PostgreSQL] 8. N일 후 지속률 산출하기(종합쿼리 첨부) 🎯 등록 시점을 기준으로 일정 기간 동안 사용자가 지속해서 사용하고 있는지를 조사할 때 지속률과 정착률을 사용하면 경향을 쉽게 파악할 수 있다. 지속률과 정착률이란? 지속률 : 등록일 기준으로 이후 지정일 동안 사용자가 서비스를 얼마나 이용했는지 나타내는 지표 등록일 이후 매일 서비스를 사용하지 않더라도, 판정 날짜 안에 사용했다면 지속자로 취급함 집계 방법 : 등록자와 사용자 수를 집계하고, (사용자 수 / 등록 수) 로 구한다. 정착률 : 등록일 기준으로 이후 지정한 7일 동안 사용자가 서비스를 사용했는지 나타내는 지표 7일이라는 기간 동안 한 번이라도 서비스를 사용했다면 정착자로 다룸 7일 정착률은 등록 후 1일부터 7일까지의 정착률을 기준으로 산출함 14일 정착률은 이후 7일(등록일부터 8일 후부.. 2024. 2. 1.
[PostgreSQL] 7. 사용자 등록 수의 추이와 경향 보기 다음과 같이 SNS 서비스에서 수집된 데이터의 예시로 실습한다. 1. 등록 수의 추이와 경향 보기 사용자 등록이 필요한 서비스에서 등록 수는 중요한 지표이다. 등록자가 감소 경향을 보인다면 서비스를 활성화하기 어려워진다는 의미이고, 반대로 등록자가 증가 경향을 보인다면 사용자가 서비스에서 이탈할지 아닐지를 분석해서 서비스 활성화와 연결할 수 있어야 한다. 사용자 이탈 여부를 살피기 전에, 먼저 현재 등록 수를 파악해본다. ▶︎ 날짜별 등록 수의 추이 SELECT register_date , count(DISTINCT user_id) AS register_count FROM mst_users GROUP BY 1 ORDER BY 1 ▶︎ 월별 등록 수의 추이 등록 날짜에서 연-월을 추출하고, 이를 기준으로 .. 2024. 1. 30.
[PostgreSQL] 6. RFM 분석으로 사용자 그룹 나누기 RFM 분석 Recency : 최근 구매일 최근에 무언가를 구매한 사용자를 우량 고객으로 취급 Frequency : 구매 횟수 사용자가 구매한 횟수를 세고, 많을수록 우량 고객으로 취급 Monetary : 구매 금액 합계 사용자의 구매 금액 합계를 집계하고, 금액이 높을수록 우량 고객으로 취급 👉 지난 포스팅에서 살펴본 Decile 분석에서는 한 번의 구매로 비싼 물건을 구매한 사용자와 정기적으로 저렴한 물건을 여러 번 구매한 사용자가 같은 그룹으로 판정되기도 한다. 하지만 RFM 분석은 이러한 사용자들도 구분할 수 있다. 1. RFM 구하기 고객별로 가장 최근 구매일과 구매 금액이 있다면 RFM을 구할 수 있다. max(dt)로 가장 최근 구매일을 구하고, current_date와의 날짜 차이를 구한다.. 2024. 1. 30.
728x90

티스토리툴바