英文:
Postgresql get average period from a table with different partners and purchase days
问题
Sure, here are the translations of the relevant parts:
"我有一张表格，其中包含合作伙伴和他们下订单的日期。"
"我想获取每个合作伙伴购买周期的信息，例如："
"如何获取这些间隔？"
Please note that translating code or technical terms can be challenging, and it's often best to use the original terms or provide context in English to ensure clarity.
英文:
So basically I have a table where are partners and the dates when then they did their orders.
| partner_id | order_date | |
|---|---|---|
| 1 | 2022-01-02 | |
| 1 | 2022-01-20 | |
| 2 | 2022-02-20 | |
| 4 | 2022-01-15 | |
| 4 | 2022-01-17 | |
| 4 | 2022-01-30 |
And I want to have an information of each partner average of purchase period, e.g.
| partner_id | period | |
|---|---|---|
| 1 | 18 | |
| 2 | 0 | |
| 4 | 8 |
How do I get these:
1 ID partner - (2022-01-20 - 2022-01-02)
2 ID partner - 0
3 ID partner - avg(2022-01-15 - 2022-01-17) + (2022-01-17 - 2022-01-30)))
Would it be possible and how to get these intervals?
答案1
得分: 2
你可以使用 lag() 来获取前一个日期，然后进行聚合：
select partner_id,
avg(order_date - lag_order_date) as avg_date_diff
from (
select p.*,
lag(order_date) over(partition by partner_id order by order_date) lag_order_date
from partners p
) p
group by partner_id
请注意，当没有前一行时，lag() 返回 null，然后 avg() 会忽略这些 null 值；这似乎是你想要的行为。
英文:
You can use lag() to get the previous date, then aggregate:
select partner_id,
avg(order_date - lag_order_date) as avg_date_diff
from (
select p.*,
lag(order_date) over(partition by partner_id order by order_date) lag_order_date
from partners p
) p
group by partner_id
Note that lag() returns null when there is no previous row, which avg() then ignores; this seems to be what you want.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论