我希望按发布日期分组计算销售额,但也按销售时产品的年龄进行分组,如下所示:
| 3 months | 6 months
2015-01 | 28.1 | 37.1
2015-02 | 29.3 | 35.6
因此,对于2015 - 01年发布的产品,28.1是每种类型,在发布后3个月销售的平均产品数量 . 在发布日期后6个月,显然有更多的产品销售,37.1 .
以下SQL获取销售列表:
SELECT
d.item AS title,
d.quantity,
a.firstdate AS release_date,
i.date AS invoice_date,
i.date - a.firstdate AS age
FROM invoices i
JOIN invoice_details d ON i.id = d.invoice_id
JOIN (SELECT
d.item,
d.binding,
min(i.date) AS firstdate
FROM invoices i
JOIN invoice_details d ON i.id = d.invoice_id
GROUP BY d.item, d.binding) AS a ON a.item = d.item AND a.binding = d.binding
WHERE
i.discount != 100 AND d.price > 0
AND (d.binding != 'Hardback' OR d.binding != 'Ebooks')
ORDER BY title, invoice_date
结果看起来像:
title | quantity | release date | invoice date | age
A | 1 | 2013-11-14 | 2013-11-14 | 0
A | 2 | 2013-11-14 | 2013-12-14 | 30
A | 3 | 2013-11-14 | 2014-01-14 | 60
A | 4 | 2013-11-14 | 2014-02-14 | 90
A | 5 | 2013-11-14 | 2014-03-14 | 120
B | 6 | 2013-11-14 | 2013-11-14 | 0
B | 7 | 2013-11-14 | 2013-12-14 | 30
B | 8 | 2013-11-14 | 2014-01-14 | 60
B | 9 | 2013-11-14 | 2014-02-14 | 90
B | 10 | 2013-11-14 | 2014-03-14 | 120
对于产品A,2013-11-14发布日期后3个月的总销售额为1 2 3 = 6 . 对于产品B,3个月后的总销售额为6 7 8 = 21 . 2013-11季度,3个月后每个季度的平均销售额为(6 21)/2=13.5
之后6个月((1 2 3 4 5)(6 7 8 9 10))/ 2 = 27.5
发布日期只是产品销售的第一个日期 - 这就是加入的子查询的用途 . 可能有更好的方法 .
我试过这个来获得3,6,12和24个月的平均值:
SELECT
to_char(a.release_date, 'YYYY-MM') AS release_date,
avg(CASE WHEN i.date - a.release_date < 92
THEN d.quantity END) AS three_months,
avg(CASE WHEN i.date - a.release_date < 183
THEN d.quantity END) AS six_months,
avg(CASE WHEN i.date - a.release_date < 365
THEN d.quantity END) AS twelve_months,
avg(CASE WHEN i.date - a.release_date < 730
THEN d.quantity END) AS twentyfour_months
FROM invoices i
JOIN invoice_details d ON i.id = d.invoice_id
JOIN (SELECT
d.item,
d.binding,
min(i.date) AS release_date
FROM invoices i
JOIN invoice_details d ON i.id = d.invoice_id
GROUP BY d.item, d.binding) AS a ON a.item = d.item AND a.binding = d.binding
WHERE
i.discount != 100 AND d.price != 0
AND (d.binding != 'Hardback' OR d.binding != 'Ebooks')
GROUP BY release_date
ORDER BY release_date desc
显然这是完全错误的,因为它没有按 Headers 对结果进行分组 . 它给了我每个订单的平均项目,而不是每个 Headers 的平均项目 .
顺便说一句,我被困在Postgres 8.2上 .
1 回答
如果我理解正确,这就是你想要的:
这显然是未经测试的,因为我甚至不记得上次触摸8.2安装时的情况 . 您的版本没有公用表表达式或横向连接,在后续版本中列出了两个关键功能,这些功能可以使这更加直观 .
无论如何,诀窍是首先计算每张发票的相对于每本书的发行日期的年龄,然后在不同的时间段内对其进行平均 . 当我移动它们并略微改变它们时仔细查看过滤器(
(d.binding != 'Hardback' OR d.binding != 'Ebooks')
很可能不是你想要的) .