SQL计算出考虑年龄的产品销售额

Question

我想按发布日期分组的产品计算销售额，但也按产品销售时的年龄分组，如下所示:

        | 3 months | 6 months    
2015-01 | 28.1     | 37.1
2015-02 | 29.3     | 35.6

所以 28.1 是 2015 年 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

在 ((1+2+3+4+5) + (6+7+8+9+10))/2 = 27.5 之后的 6 个月

发布日期只是产品销售的第一天 - 这就是连接子查询的用途。可能有更好的方法。

我试过这个以获得 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

显然这是完全错误的，因为它没有按标题对结果进行分组。它为我提供了每个订单的平均商品数，而不是每个标题的平均商品数。

顺便说一句，我卡在 Postgres 8.2 上了。

Answer 1

如果我没理解错的话，这就是你想要的:

SELECT
  to_char(date, 'YYYY-MM') AS release_date,
  avg(CASE WHEN age <  92 THEN quantity ELSE 0 END) AS three_months,
  avg(CASE WHEN age < 183 THEN quantity ELSE 0 END) AS six_months,
  avg(CASE WHEN age < 365 THEN quantity ELSE 0 END) AS twelve_months,
  avg(CASE WHEN age < 730 THEN quantity ELSE 0 END) AS twentyfour_months
FROM (
  SELECT d.item, d.quantity, (i.date - first_release.date) AS age, fr.date
  FROM invoice_details d
  JOIN (
    SELECT d.item, min(i.date) AS date
    FROM invoice_details d
    JOIN invoices i ON i.id = d.invoice_id
    WHERE d.binding != 'Hardback' AND d.binding != 'Ebooks'
    GROUP BY d.item) AS fr USING (item)
  JOIN invoice i ON i.id = d.invoice_id
  WHERE i.discount != 100 AND d.price > 0) AS foo
GROUP BY release_date
ORDER BY release_date;

这显然是未经测试的，因为我什至不记得上次接触 8.2 安装是什么时候。您的版本没有公用表表达式或横向联接，这些是以后版本中的两个关键功能，它们会使这更直观。

无论如何，诀窍是首先计算每张发票相对于每本书售出日期的账龄，然后将其在不同时间段内取平均值。仔细查看过滤器，因为我移动它们并稍微改变了它们((d.binding != 'Hardback' OR d.binding != 'Ebooks') 很可能不是您想要的)。