SQL:分组average-if / case SELECT语句

Question

我有一个数据库，看起来像这样的SQL提琴:http://sqlfiddle.com/#!9/aa02e/1

CREATE TABLE Table1
    (`Store` varchar(1), `Date` date, `Product` varchar(2), `Weekday` int, `Month` int, `Revenue` float)
;
INSERT INTO Table1
    (`Store`, `Date`, `Product`, `Weekday`, `Month`, `Revenue`)
VALUES
    ('a', '20160101', 'aa', 5, 1, 1.5),
    ('a', '20160101', 'bb', 5, 1, 4),
    ('a', '20160101', 'cc', 5, 1, 3.5),
    ('a', '20160108', 'dd', 5, 1, 2.5),
    ('a', '20160108', 'ee', 5, 1, 5),
    ('b', '20160204', 'aa', 4, 2, 9.5),
    ('b', '20160204', 'bb', 4, 2, 4),
    ('b', '20160204', 'cc', 4, 2, 3),
    ('b', '20160211', 'dd', 4, 2, 1.5),
    ('b', '20160211', 'ee', 4, 2, 2.5)
;
 SELECT * FROM table1;
+-------+------------+---------+---------+-------+---------+
| Store | Date       | Product | Weekday | Month | Revenue |
+-------+------------+---------+---------+-------+---------+
| a     | 2016-01-01 | aa      |       5 |     1 |     1.5 |
| a     | 2016-01-01 | bb      |       5 |     1 |       4 |
| a     | 2016-01-01 | cc      |       5 |     1 |     3.5 |
| a     | 2016-01-08 | dd      |       5 |     1 |     2.5 |
| a     | 2016-01-08 | ee      |       5 |     1 |       5 |
| b     | 2016-02-04 | aa      |       4 |     2 |     9.5 |
| b     | 2016-02-04 | bb      |       4 |     2 |       4 |
| b     | 2016-02-04 | cc      |       4 |     2 |       3 |
| b     | 2016-02-11 | dd      |       4 |     2 |     1.5 |
| b     | 2016-02-11 | ee      |       4 |     2 |     2.5 |
+-------+------------+---------+---------+-------+---------+

它显示了商店的收入数据，包括产品、日期和相应的日/月。我想选择以下内容:

• 月收入总额(即1月份商店a的总收入是多少?)
• 工作日平均收入(即商店a在周四的平均收入是多少?)

第一项和第二项很简单，但最后一项我有问题。目前，它对所有产品和所有日期取平均值(假设工作日匹配)。我需要的是以下步骤:

• 汇总商店和特定日期的所有收入(例如，商店b: 2月4日为9.5+4+3=16.5,2月11日为1.5+2.5=4)，如果该日期与工作日相同(这里是星期四)
• 取两个值的平均值(例如avg(16.5,4)=10.25)

我怎么才能做到呢?谢谢你

下面是查询:

SELECT
  Store,
  SUM(CASE WHEN Month = 1 THEN Revenue ELSE NULL END) AS REVENUE_JAN,
  SUM(CASE WHEN Month = 2 THEN Revenue ELSE NULL END) AS REVENUE_FEB,
  AVG(CASE WHEN Weekday = 4 THEN Revenue ELSE NULL END) AS REVENUE_THU,
  AVG(CASE WHEN Weekday = 5 THEN Revenue ELSE NULL END) AS REVENUE_FRI
FROM Table1
GROUP BY
  Store
;

Answer 1

工作日平均值很棘手。您的查询正在获取每个工作日的平均"订单大小"。但是你想要的是总收入。

一种方法是首先按工作日进行聚合，但这有点混乱。相反，您可以使用以下计算平均值的技巧:将总收入除以天数:

SELECT Store,
       SUM(CASE WHEN Month = 1 THEN Revenue ELSE NULL END) AS REVENUE_JAN,
       SUM(CASE WHEN Month = 2 THEN Revenue ELSE NULL END) AS REVENUE_FEB,
       (SUM(CASE WHEN Weekday = 4 THEN Revenue END) /
        COUNT(DISTINCT CASE WHEN Weekday = 4 THEN Date END)
       ) AS REVENUE_THU,
       (SUM(CASE WHEN Weekday = 5 THEN Revenue END) /
        COUNT(DISTINCT CASE WHEN Weekday = 5 THEN Date END)
       ) AS REVENUE_FRI
FROM Table1
GROUP BY Store;

Answer 2

SELECT
  t1.store,
  SUM(CASE WHEN Month = 1 THEN Revenue ELSE NULL END) AS REVENUE_JAN,
  SUM(CASE WHEN Month = 2 THEN Revenue ELSE NULL END) AS REVENUE_FEB,
  daily.REVENUE_THU,
  daily.REVENUE_FRI
FROM Table1 t1
JOIN (
  SELECT
    Store,
    weekday,
    avg(CASE WHEN weekday = 4 THEN sum_rev END) as REVENUE_THU,
    avg(CASE WHEN weekday = 5 THEN sum_rev END) as REVENUE_FRI
  FROM (
    SELECT
      Store, date, weekday,
      SUM(revenue) AS sum_rev
    FROM Table1
    GROUP BY
      Store, date, weekday
  ) AS foo
  GROUP BY Store, weekday
) AS daily ON daily.store = t1.store
GROUP BY
  t1.store

Answer 3

这个解决方案怎么样?它返回所选商店所选日期的平均值

CREATE PROCEDURE sumForDayStore(IN vday INTEGER, IN vStore VARCHAR(50))
BEGIN
    DECLARE totalDays INTEGER;
    DECLARE totalRevenu INTEGER;
    SET totalDays = (SELECT count(*) FROM Table1 WHERE WeekDay = vDay AND store = vStore);
    SET totalRevenu = (SELECT sum(Revenue) FROM Table1 WHERE WeekDay = vDay AND store = vStore);
    SELECT totalRevenu/totalDays;
END;

调用sumForDayStore (5, ' ');

Answer 4

这个怎么样:

SELECT mnth.Store, REVENUE_JAN, REVENUE_FEB, avg(rthu) REVENUE_THU, avg(rfri) REVENUE_FRI
FROM 
(Select Store, sum(case when Month = 1 then Revenue else NULL END) REVENUE_JAN, 
sum(case when Month = 2 then Revenue else NULL END) REVENUE_FEB 
From Table1 group by Store) as mnth 
join 
(Select Store, sum(case when Weekday = 4 then Revenue end) rThu,
sum(case when Weekday = 5 then Revenue end) rFri from Table1 group by Store, Date) as dys 
on mnth.Store = dys.Store
group by mnth.Store, REVENUE_JAN, REVENUE_FEB

我将此查询的性能与第一个答案中的查询进行了比较，根据SQL server执行计划，它显示出更好的性能(快1.6倍)。也许这对更大的数据集有帮助。