CREATE TEMP TABLE transaction (buyer_id int, tstamp timestamp);
INSERT INTO transaction VALUES
(1,'2012-01-03 20:00')
,(1,'2012-01-05 20:00')
,(1,'2012-01-07 20:00') -- multiple transactions this month
,(1,'2012-02-03 20:00') -- next month
,(1,'2012-03-05 20:00') -- next month
,(2,'2012-01-07 20:00')
,(2,'2012-03-07 20:00') -- not next month
,(3,'2012-01-07 20:00') -- just once
,(4,'2012-02-07 20:00'); -- just once
2. 쿼리(월별, 연속구매여부)
WITH t AS (
SELECT
buyer_id -- 구매 유저
,date_trunc('month', tstamp) AS month -- 구매 발생 월
,count(*) AS item_transactions -- 구매 회수
,lag(date_trunc('month', tstamp)) OVER (PARTITION BY buyer_id ORDER BY date_trunc('month', tstamp)) = date_trunc('month', tstamp) - interval '1 month' OR NULL AS repeat_transaction -- 이전 구매 발생이 1달전인가?
FROM
transaction WHERE
tstamp >= '2012-01-01'::date AND
tstamp < '2012-05-01'::date -- time range of interest. GROUP
BY 1, 2
) SELECT month ,sum(item_transactions) AS num_trans -- 거래수
,count(*) AS num_buyers -- 구매 유저수
,count(repeat_transaction) AS repeat_buyers -- 재구매 유저수
,round( CASE WHEN sum(item_transactions) > 0 THEN count(repeat_transaction) / sum(item_transactions) * 100 ELSE 0 END, 2) AS buyer_retention -- 재구매 비율
FROM t GROUP BY 1 ORDER BY 1;
결과
month | num_trans | num_buyers | repeat_buyers | buyer_retention_pct
---------+-----------+------------+---------------+--------------------
2012-01 | 5 | 3 | 0 | 0.00
2012-02 | 2 | 2 | 1 | 50.00
2012-03 | 2 | 2 | 1 | 50.00
http://stackoverflow.com/questions/16492842/how-to-calculate-retention-month-over-month-using-sql
https://www.postgresql.org/docs/9.6/static/functions-window.html
http://stackoverflow.com/questions/17301816/lag-function-and-group-by
'DBMS, noSQL > PostgreSQL' 카테고리의 다른 글
| dblink 문법 (0) | 2017.10.25 |
|---|---|
| Postgres 백업 및 복구 ( Backup & Restore with pgAdmin) (0) | 2017.07.21 |
| Postgres CPU 과다 사용 ( high CPU Usage ) (0) | 2017.01.23 |
| Postgres , CSV를 Table로 import 하는 경우에 에러 발생 (0) | 2016.12.06 |
| ERD 그리기, SQL Power Architect (0) | 2016.11.14 |
StartGuide
I want to share the basic to programming of each category and how to solve the error. This basic instruction can be extended further. And I have been worked in southeast Asia more than 3 years. And I want to have the chance to work another country.
Todo.java
