day.sql
4.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
select
A.productId,
sum(`2019-03-01`) AS '2019-03-01',
sum(`2019-03-02`) AS '2019-03-02',
sum(`2019-03-03`) AS '2019-03-03',
sum(`2019-03-04`) AS '2019-03-04',
sum(`2019-03-05`) AS '2019-03-05',
sum(`2019-03-06`) AS '2019-03-06',
sum(`2019-03-07`) AS '2019-03-07',
sum(`2019-03-08`) AS '2019-03-08',
sum(`2019-03-09`) AS '2019-03-09',
sum(`2019-03-10`) AS '2019-03-10',
sum(`2019-03-11`) AS '2019-03-11',
sum(`2019-03-12`) AS '2019-03-12',
sum(`2019-03-13`) AS '2019-03-13',
sum(`2019-03-14`) AS '2019-03-14',
sum(`2019-03-15`) AS '2019-03-15',
sum(`2019-03-16`) AS '2019-03-16',
sum(`2019-03-17`) AS '2019-03-17',
sum(`2019-03-18`) AS '2019-03-18',
sum(`2019-03-19`) AS '2019-03-19',
sum(`2019-03-20`) AS '2019-03-20',
sum(`2019-03-21`) AS '2019-03-21',
sum(`2019-03-22`) AS '2019-03-22',
sum(`2019-03-23`) AS '2019-03-23',
sum(`2019-03-24`) AS '2019-03-24',
sum(`2019-03-25`) AS '2019-03-25',
sum(`2019-03-26`) AS '2019-03-26',
sum(`2019-03-27`) AS '2019-03-27',
sum(`2019-03-28`) AS '2019-03-28',
sum(`2019-03-29`) AS '2019-03-29',
sum(`2019-03-30`) AS '2019-03-30',
sum(`2019-03-31`) AS '2019-03-31'
from
(
select
productId,
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-01' then 1 else 0 end as '2019-03-01',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-02' then 1 else 0 end as '2019-03-02',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-03' then 1 else 0 end as '2019-03-03',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-04' then 1 else 0 end as '2019-03-04',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-05' then 1 else 0 end as '2019-03-05',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-06' then 1 else 0 end as '2019-03-06',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-07' then 1 else 0 end as '2019-03-07',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-08' then 1 else 0 end as '2019-03-08',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-09' then 1 else 0 end as '2019-03-09',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-10' then 1 else 0 end as '2019-03-10',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-11' then 1 else 0 end as '2019-03-11',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-12' then 1 else 0 end as '2019-03-12',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-13' then 1 else 0 end as '2019-03-13',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-14' then 1 else 0 end as '2019-03-14',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-15' then 1 else 0 end as '2019-03-15',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-16' then 1 else 0 end as '2019-03-16',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-17' then 1 else 0 end as '2019-03-17',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-18' then 1 else 0 end as '2019-03-18',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-19' then 1 else 0 end as '2019-03-19',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-20' then 1 else 0 end as '2019-03-20',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-21' then 1 else 0 end as '2019-03-21',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-22' then 1 else 0 end as '2019-03-22',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-23' then 1 else 0 end as '2019-03-23',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-24' then 1 else 0 end as '2019-03-24',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-25' then 1 else 0 end as '2019-03-25',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-26' then 1 else 0 end as '2019-03-26',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-27' then 1 else 0 end as '2019-03-27',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-28' then 1 else 0 end as '2019-03-28',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-29' then 1 else 0 end as '2019-03-29',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-30' then 1 else 0 end as '2019-03-30',
case DATE_FORMAT(time,'%Y-%m-%d') when '2019-03-31' then 1 else 0 end as '2019-03-31'
from
buyers
where
time >= '2019-03-01' and time < '2019-04-01'
) A
group by A.productId