首页 文章

SQL考虑到年龄,按产品计算销售额

提问于
浏览
2

我希望按发布日期分组计算销售额,但也按销售时产品的年龄进行分组,如下所示:

| 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 回答

  • 0

    如果我理解正确,这就是你想要的:

    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') 很可能不是你想要的) .

相关问题