· 6 years ago · Mar 11, 2019, 01:22 AM
1ORDR DAY COUNT AGGREGATE AVERAGE PREVIOUS PERIOD VOTE VELOCITY (COALESCE(T_1.COUNT, 0) - COALESCE(T_2.COUNT, 0)) * 100.0/ COALESCE (T_2.COUNT, 0)
2 16 August, 11 2012 1 1 0 (null)
3 17 August, 12 2012 1 2 0.0625 (null)
4 18 August, 13 2012 4 9 0.2941 0
5 19 August, 14 2012 1 13 0.6667 600
6
7ORDR DAY COUNT AGGREGATE AVERAGE PREVIOUS PERIOD VOTE VELOCITY (COALESCE(T_1.COUNT, 0) - COALESCE(T_2.COUNT, 0)) * 100.0/ COALESCE (T_2.COUNT, 0)
8 14 2012-08-11 1 1 0.0000 50.00000
9 15 2012-08-12 1 2 0.0714 -66.66667
10 1 2012-08-13 6 8 NULL NULL
11 2 2012-08-14 3 11 8.0000 NULL
12
13drop table if exists sampleData;
14create table sampleData
15(
16 id int NOT NULL AUTO_INCREMENT,
17 timecode int,
18 count int,
19 PRIMARY KEY (id)
20 )
21-- #ENGINE=MyISAM
22;
23INSERT INTO sampleData
24(timecode, count)
25VALUES
26(1344893440, 1), ( 1346014720, 1),( 1344898688,1),( 1345654784,1),( 1345978368,1),
27( 1345959296,1), (1345064704,1), ( 1345156352,1),( 1345225600,1),
28(1345017984,1),( 1345640960,1),( 1346019968,1),( 1345834752,1),
29( 1345438464,1),( 1344986880,1),( 1345045632,1),( 1345557888,1),( 1344973056,1),( 1345087232,1),( 1345433216,1),( 1345691008,1),
30( 1344917760,1),( 1345253248,1),( 1344934912,1),( 1345890048,1),( 1345272448,1), (1345829504,1),( 1345798400,1),( 1345203200,1),( 1344741120,1),
31( 1345175552,1),( 1344824192,1),( 1344926336,1),( 1345571712,1),( 1344931584,1),( 1345211776,1),( 1345059456,1),( 1345516288,1),( 1345441920,1),( 1346009472,1);
32
33
34set @t3.id=0;
35set @t3.id2=0; -- must differentiate the ids because if not, they will increase and will not match. That is, there will not be 3 identical columns but 1 very long column.
36set @t3.id3=0;
37select t.*,
38 (coalesce(t_1.count, 0) - coalesce(t_2.count, 0)) * 100.0/ coalesce (t_2.count, 0)
39from
40 (SELECT @t3.id:=@t3.id+1 AS ordr,
41 t3.day, t3.count , SUM(t2.count) AS AGGREGATE , (SUM(t2.count)-t3.count)/(@t3.id) AS "average previous period vote velocity"
42 -- (count(t3.count)-1) as "number of people voted",
43 FROM
44 (
45 SELECT t1.id, t1.hour, t1.day, sum(t1.count ) AS COUNT
46 FROM
47 (
48 (SELECT id, hour(time(FROM_UNIXTIME( timecode))) AS hour,
49 date(FROM_UNIXTIME( timecode)) AS DAY,
50 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
51 FROM sampleData
52 WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
53 GROUP BY DAY)
54 UNION ALL
55 (SELECT id, hour(time(FROM_UNIXTIME( timecode))) AS hour,
56 date(FROM_UNIXTIME( timecode-86400) ) AS DAY,
57 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
58 FROM sampleData
59 WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
60 GROUP BY DAY)
61 ) t1
62 GROUP BY t1.DAY -- no need count
63 HAVING COUNT(*)>0
64 ORDER BY t1.DAY
65 ) t3
66 INNER JOIN
67 (
68 (SELECT hour(time(FROM_UNIXTIME( timecode))) AS hour,
69 date(FROM_UNIXTIME( timecode)) AS DAY,
70 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
71 FROM sampleData
72 WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
73 GROUP BY DAY)
74 UNION ALL
75 (SELECT hour(time(FROM_UNIXTIME( timecode))) AS hour,
76 date(FROM_UNIXTIME( timecode-86400) ) AS DAY,
77 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
78 FROM sampleData
79 WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
80 GROUP BY DAY)
81 ) t2
82 ON t3.DAY>=t2.DAY
83 GROUP BY t3.DAY, t3.COUNT
84 ORDER BY t3.DAY)t
85left outer join
86 (SELECT @t3.id2:=@t3.id2+1 AS ordr,
87 t3.day, t3.count , SUM(t2.count) AS AGGREGATE , (SUM(t2.count)-t3.count)/(@t3.id2) AS "average previous period vote velocity"
88 -- (count(t3.count)-1) as "number of people voted",
89 FROM
90 (
91 SELECT t1.id as id2, t1.hour, t1.day, sum(t1.count ) AS COUNT
92 FROM
93 (
94 (SELECT id, hour(time(FROM_UNIXTIME( timecode))) AS hour,
95 date(FROM_UNIXTIME( timecode)) AS DAY,
96 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
97 FROM sampleData
98 WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
99 GROUP BY DAY)
100 UNION ALL
101 (SELECT id, hour(time(FROM_UNIXTIME( timecode))) AS hour,
102 date(FROM_UNIXTIME( timecode-86400) ) AS DAY,
103 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
104 FROM sampleData
105 WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
106 GROUP BY DAY)
107 ) t1
108 GROUP BY t1.DAY -- no need count
109 HAVING COUNT(*)>0
110 ORDER BY t1.DAY
111 ) t3
112 INNER JOIN
113 (
114 (SELECT hour(time(FROM_UNIXTIME( timecode))) AS hour,
115 date(FROM_UNIXTIME( timecode)) AS DAY,
116 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
117 FROM sampleData
118 WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
119 GROUP BY DAY)
120 UNION ALL
121 (SELECT hour(time(FROM_UNIXTIME( timecode))) AS hour,
122 date(FROM_UNIXTIME( timecode-86400) ) AS DAY,
123 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
124 FROM sampleData
125 WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
126 GROUP BY DAY)
127 ) t2
128 ON t3.DAY>=t2.DAY
129 GROUP BY t3.DAY, t3.COUNT
130 ORDER BY t3.DAY) t_1
131 on t.ordr = t_1.ordr + 1
132left outer join
133 (SELECT @t3.id3:=@t3.id3+1 AS ordr,
134 t3.day, t3.count , SUM(t2.count) AS AGGREGATE , (SUM(t2.count)-t3.count)/(@t3.id3) AS "average previous period vote velocity"
135 -- (count(t3.count)-1) as "number of people voted",
136 FROM
137 (
138 SELECT t1.id as id3, t1.hour, t1.day, sum(t1.count ) AS COUNT
139 FROM
140 (
141 (SELECT id, hour(time(FROM_UNIXTIME( timecode))) AS hour,
142 date(FROM_UNIXTIME( timecode)) AS DAY,
143 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
144 FROM sampleData
145 WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
146 GROUP BY DAY)
147 UNION ALL
148 (SELECT id, hour(time(FROM_UNIXTIME( timecode))) AS hour,
149 date(FROM_UNIXTIME( timecode-86400) ) AS DAY,
150 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
151 FROM sampleData
152 WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
153 GROUP BY DAY)
154 ) t1
155 GROUP BY t1.DAY -- no need count
156 HAVING COUNT(*)>0
157 ORDER BY t1.DAY
158 ) t3
159 INNER JOIN
160 (
161 (SELECT hour(time(FROM_UNIXTIME( timecode))) AS hour,
162 date(FROM_UNIXTIME( timecode)) AS DAY,
163 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
164 FROM sampleData
165 WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
166 GROUP BY DAY)
167 UNION ALL
168 (SELECT hour(time(FROM_UNIXTIME( timecode))) AS hour,
169 date(FROM_UNIXTIME( timecode-86400) ) AS DAY,
170 (FROM_UNIXTIME( timecode)) AS original, COUNT(1) AS 'count'
171 FROM sampleData
172 WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
173 GROUP BY DAY)
174 ) t2
175 ON t3.DAY>=t2.DAY
176 GROUP BY t3.DAY, t3.COUNT
177 ORDER BY t3.DAY) t_2
178 on t.ordr = t_2.ordr + 2