MySQL按日期分组，不存在则强制返回空值

Question

我建立了一个查询，让我返回一些应用程序的平均排名。

但对于其中一些，有一个月我们没有数据，因为该应用是新应用(假设该应用从本月开始发布，因此我们从本月开始收集数据)

SELECT 
    DATE_FORMAT(date, '%Y-%m'),
    app_id,
    AVG(rank)
FROM wadstats.applestore_ranking
where app_id IN (100,
        2,
        3,
        4,
        5,
        6)
GROUP BY MONTH(date), app_id
ORDER BY CASE WHEN app_id = 100 THEN 1 ELSE 2 END, date ASC

我需要先显示 app_id = 100

但是对于 app_id = 8，例如我没有八月的数据。

然后结果看起来像

'2015-07', '100', '3.9355'
'2015-04', '100', '49.5000'
'2015-08', '100', '5.2258'
'2015-05', '100', '16.3333'
'2015-09', '100', '6.1333'
'2015-06', '100', '7.5667'
'2015-10', '100', '5.7727'
'2015-04', '2', '6.0000'
'2015-08', '2', '9.8710'
'2015-05', '2', '6.4667'
'2015-09', '2', '8.9667'
'2015-06', '2', '8.5333'
'2015-10', '2', '9.9545'
'2015-07', '2', '10.5806'
'2015-05', '3', '56.3929'
'2015-09', '3', '55.1667'
'2015-06', '3', '35.2500'
'2015-07', '3', '38.7143'
'2015-04', '3', '38.7500'
'2015-08', '3', '52.5500'
'2015-09', '4', '30.2105'
'2015-06', '4', '27.9231'
'2015-10', '4', '30.0000'
'2015-07', '4', '47.0000'
'2015-08', '4', '32.6818'
'2015-06', '5', '46.8667'
'2015-10', '5', '86.6667'
'2015-07', '5', '63.5185'
'2015-04', '5', '24.2500'
'2015-08', '5', '67.3571'
'2015-10', '6', '30.1818'

我希望每个月都为空，即使这个特定月份没有可用数据也是如此

预期结果

'2015-07', '100', '3.9355'
'2015-04', '100', '49.5000'
'2015-08', '100', '5.2258'
'2015-05', '100', '16.3333'
'2015-09', '100', '6.1333'
'2015-06', '100', '7.5667'
'2015-10', '100', '5.7727'
'2015-04', '2', '6.0000'
'2015-08', '2', '9.8710'
'2015-05', '2', '6.4667'
'2015-09', '2', '8.9667'
'2015-06', '2', '8.5333'
'2015-10', '2', '9.9545'
'2015-07', '2', '10.5806'
'2015-05', '3', '56.3929'
'2015-09', '3', '55.1667'
'2015-06', '3', '35.2500'
'2015-07', '3', '38.7143'
'2015-04', '3', '38.7500'
'2015-08', '3', '52.5500'
'2015-09', '4', '30.2105'
'2015-06', '4', '27.9231'
'2015-05', '4', NULL
'2015-10', '4', '30.0000'
'2015-07', '4', '47.0000'
'2015-08', '4', '32.6818'
'2015-06', '5', '46.8667'
'2015-10', '5', '86.6667'
'2015-07', '5', '63.5185'
'2015-04', '5', '24.2500'
'2015-08', '5', '67.3571'
'2015-04', '6', NULL
'2015-05', '6', NULL
'2015-06', '6', NULL
'2015-07', '6', NULL
'2015-08', '6', NULL
'2015-09', '6', NULL
'2015-10', '6', '30.1818'

如果我需要有 0 而不是 NULL，那也可以，但是我需要在数据库中的每个月都有，以便为每个 app_id 赋值

提前致谢

Answer 1

下面的查询使用派生表(别名 inr)将 YearMonth/app_id 组合放在一起。然后它在 left join 中使用它来获取数据无论它是否存在于表 applestore_ranking 中。

如果您希望出现零而不是 NULL，请使用 ifnull()。因此，该部分将变为 ifnull(AVG(r.rank),0) as Rank。

请注意，将这个阶段简单地放在一起然后突出显示 inr 单独选择的代码并查看其简单输出会有所帮助。这将使左连接更容易理解。

Helper表的概念一直在sql中使用。有时它们会在运行中放在一起，然后丢弃。其他时候它们是永久性的。

架构

create schema appleSandbox;
use appleSandbox;

-- drop table applestore_ranking;
create table applestore_ranking
(   id int auto_increment primary key,
    app_id int not null,
    date date not null, -- not a great column name
    rank int not null
);
-- truncate table applestore_ranking;
insert applestore_ranking (app_id,date,rank) values
(2,'2015-08-01',1),(2,'2015-09-05',10),(2,'2015-09-12',11),(2,'2015-10-01',14),
(6,'2015-10-01',7),(6,'2015-10-05',6),(6,'2015-10-14',2),
(100,'2015-09-01',16),(100,'2015-10-01',16),(100,'2015-10-05',17),(100,'2015-10-14',18);

create table monthHelper
(   -- load this up with a few years worth
    id int auto_increment primary key,
    theDate date not null, -- slightly better column name   
    wantToSee int not null -- do we want to see it in results or not? 0=no, 1=yes
);

-- note only a few wantToSee have been turned on to 1
insert monthHelper(theDate,wantToSee) values
('2015-05-01',0),('2015-06-01',0),('2015-07-01',0),('2015-08-01',1),('2015-09-01',1),('2015-10-01',1),('2015-11-01',0),('2015-12-01',0),
('2016-01-01',0),('2016-02-01',0),('2016-03-01',0); -- etc

查询

SELECT DATE_FORMAT(inr.theDate, '%Y-%m') as YearMonth, inr.app_id, AVG(r.rank) as Rank
FROM
(   select distinct mh.theDate,r.app_id
    from monthhelper mh
    cross join applestore_ranking r
    where mh.wantToSee=1
    and r.app_id IN (100,2,3,4,5,6)
) inr
left join applestore_ranking r
on r.app_id=inr.app_id and year(inr.theDate)=year(r.date) and month(inr.theDate)=month(r.date)
GROUP BY MONTH(inr.theDate), inr.app_id
ORDER BY CASE WHEN inr.app_id = 100 THEN 1 ELSE 2 END, inr.theDate ASC

结果

+-----------+--------+---------+
| YearMonth | app_id | Rank    |
+-----------+--------+---------+
| 2015-08   |    100 |    NULL |
| 2015-09   |    100 | 16.0000 |
| 2015-10   |    100 | 17.0000 |
| 2015-08   |      2 |  1.0000 |
| 2015-08   |      6 |    NULL |
| 2015-09   |      2 | 10.5000 |
| 2015-09   |      6 |    NULL |
| 2015-10   |      2 | 14.0000 |
| 2015-10   |      6 |  5.0000 |
+-----------+--------+---------+

清理

drop schema AppleSandbox;