在 PostgreSQL 中，我有一個訂單表，代表商店客戶的訂單：
SELECT * FROM orders
| order_id | 客戶ID | 價值 | created_at |
|---|---|---|---|
| 1 | 1 | 188.01 | 2020-11-24 |
| 2 | 2 | 25.74 | 2022-10-13 |
| 3 | 1 | 159.64 | 2022-09-23 |
| 4 | 1 | 201.41 | 2022-04-01 |
| 5 | 3 | 357.80 | 2022-09-05 |
| 6 | 2 | 386.72 | 2022-02-16 |
| 7 | 1 | 200.00 | 2022-01-16 |
| 8 | 1 | 19.99 | 2020-02-20 |
對于指定的時間范圍（例如 2022-01-01 到 2022-12-31），我需要找到以下內容：
- 平均一階價值
- 平均二階值
- 平均三階價值
- 平均四階值
例如，每個客戶的第一次購買是：
- 對于 customer_id 1，order_id 8 是他們的第一次購買
- 客戶 2，訂單 6
- 客戶 3，訂單 5
因此，第一次購買的平均訂單價值為(19.99 386.72 357.80) / 3= 254.84 美元
這也需要為第 2、3 和 4 次購買找到。
我還需要找到購買之間的平均時間：
- 訂單 1 到訂單 2
- 訂購 2 到訂購 3
- 訂購 3 到訂購 4
理想情況下，最終結果看起來像這樣：
| 訂單號 | AOV | av_days_since_last_order |
|---|---|---|
| 1 | 254.84 | 0 |
| 2 | 300.00 | 28 |
| 3 | 322.22 | 21 |
| 4 | 350.00 | 20 |
請注意，訂單 1 自上次訂單以來的平均天數始終為 0，因為它是第一次購買。
謝謝。
uj5u.com熱心網友回復：
select order_number
,round(avg(value),2) as AOV
,coalesce(round(avg(days_between_orders),0),0) as av_days_since_last_order
from
(
select *
,row_number() over(partition by customer_id order by created_at) as order_number
,created_at - lag(created_at) over(partition by customer_id order by created_at) as days_between_orders
from t
) t
where created_at between '2022-01-01' and '2022-12-31'
group by order_number
order by order_number
| 訂單號 | aov | av_days_since_last_order |
|---|---|---|
| 1 | 372.26 | 0 |
| 2 | 25.74 | 239 |
| 3 | 200.00 | 418 |
| 4 | 201.41 | 75 |
| 5 | 159.64 | 175 |
小提琴
uj5u.com熱心網友回復：
我想它應該是這樣的
WITH prep_data AS (
SELECT order_id,
cuntomer_id,
ROW_NUMBER() OVER(PARTITION BY order_id, cuntomer_id ORDER BY created_at) AS pushcase_num,
created_at,
value
FROM pushcases
WHERE created_at BETWEEN :date_from AND :date_to
), prep_data2 AS (
SELECT pd1.order_id,
pd1.cuntomer_id,
pd1.pushcase_num
pd2.created_at - pd1.created_at AS date_diff,
pd1.value
FROM prep_data pd1
LEFT JOIN prep_data pd2 ON (pd1.order_id = pd2.order_id AND pd1.cuntomer_id = pd2.cuntomer_id AND pd1.pushcase_num = pd2.pushcase_num 1)
)
SELECT order_id,
cuntomer_id,
pushcase_num,
avg(value) AS avg_val,
avg(date_diff) AS avg_date_diff
FROM prep_data2
GROUP BY pushcase_num
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/519431.html