首页 文章

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

提问于
浏览 次
1

我有一个看起来像这个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）如果该日期与工作日相同（此处为周四）

  • 取两个值的平均值（例如，平均值（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
;
mysql sql select average

4 回答

  • 1

    这个解决方案怎么样，它选择的商店选择日的平均值

    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;

    CALL sumForDayStore（5，'a'）;

    回复于
  • 0

    这个怎么样：

    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服务器执行计划显示了更好的性能（快1.6倍） . 也许这对更大的数据集会有所帮助 .

    回复于
  • 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;
    回复于
  • 0
    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
    回复于

相关问题