Created
December 2, 2012 06:05
-
-
Save gregrahn/4187174 to your computer and use it in GitHub Desktop.
Experiment to see if it is possible to rewrite the SQL query to not use a join and compute the count(distinct) in a single pass of the data.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| drop table t2; | |
| create table t2 | |
| ( | |
| day_id int not null, | |
| time_id date not null, | |
| begin_time date not null, | |
| end_time date not null | |
| ); | |
| begin | |
| for i in 0..23 loop | |
| insert into t2 values (1, date '2012-11-15' + i/24, date '2012-11-15' - 16/24 + i/24, date '2012-11-15' + 8/24 + i/24); | |
| end loop; | |
| commit; | |
| end; | |
| / | |
| drop table events; | |
| create table events | |
| ( | |
| id number not null, | |
| ts date not null | |
| ); | |
| execute dbms_random.seed(0); | |
| insert into events | |
| select trunc(dbms_random.value(100,200)) id, date '2012-11-14' + rownum/144 ts from dual connect by level <= 350; | |
| commit; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select t2.day_id, | |
| t2.time_id, | |
| count(distinct events.id) as unique_users, | |
| count(*) as total_users | |
| from t2 | |
| join events on events.ts >= t2.begin_time and events.ts < t2.end_time | |
| where t2.day_id = 1 | |
| group by t2.day_id, | |
| t2.time_id | |
| order by t2.day_id, | |
| t2.time_id ; | |
| /* | |
| DAY_ID TIME_ID UNIQUE_USERS TOTAL_USERS | |
| ---------- ------------------- ------------ ----------- | |
| 1 2012-11-15 00:00:00 75 144 | |
| 1 2012-11-15 01:00:00 75 144 | |
| 1 2012-11-15 02:00:00 77 144 | |
| 1 2012-11-15 03:00:00 77 144 | |
| 1 2012-11-15 04:00:00 78 144 | |
| 1 2012-11-15 05:00:00 78 144 | |
| 1 2012-11-15 06:00:00 78 144 | |
| 1 2012-11-15 07:00:00 78 144 | |
| 1 2012-11-15 08:00:00 78 144 | |
| 1 2012-11-15 09:00:00 77 144 | |
| 1 2012-11-15 10:00:00 77 144 | |
| 1 2012-11-15 11:00:00 77 144 | |
| 1 2012-11-15 12:00:00 75 144 | |
| 1 2012-11-15 13:00:00 75 144 | |
| 1 2012-11-15 14:00:00 73 144 | |
| 1 2012-11-15 15:00:00 74 144 | |
| 1 2012-11-15 16:00:00 75 144 | |
| 1 2012-11-15 17:00:00 76 144 | |
| 1 2012-11-15 18:00:00 76 144 | |
| 1 2012-11-15 19:00:00 77 144 | |
| 1 2012-11-15 20:00:00 77 144 | |
| 1 2012-11-15 21:00:00 77 144 | |
| 1 2012-11-15 22:00:00 77 144 | |
| 1 2012-11-15 23:00:00 76 144 | |
| 24 rows selected. | |
| */ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select * from t2; | |
| /* | |
| DAY_ID TIME_ID BEGIN_TIME END_TIME | |
| ---------- ------------------- ------------------- ------------------- | |
| 1 2012-11-15 00:00:00 2012-11-14 08:00:00 2012-11-15 08:00:00 | |
| 1 2012-11-15 01:00:00 2012-11-14 09:00:00 2012-11-15 09:00:00 | |
| 1 2012-11-15 02:00:00 2012-11-14 10:00:00 2012-11-15 10:00:00 | |
| 1 2012-11-15 03:00:00 2012-11-14 11:00:00 2012-11-15 11:00:00 | |
| 1 2012-11-15 04:00:00 2012-11-14 12:00:00 2012-11-15 12:00:00 | |
| 1 2012-11-15 05:00:00 2012-11-14 13:00:00 2012-11-15 13:00:00 | |
| 1 2012-11-15 06:00:00 2012-11-14 14:00:00 2012-11-15 14:00:00 | |
| 1 2012-11-15 07:00:00 2012-11-14 15:00:00 2012-11-15 15:00:00 | |
| 1 2012-11-15 08:00:00 2012-11-14 16:00:00 2012-11-15 16:00:00 | |
| 1 2012-11-15 09:00:00 2012-11-14 17:00:00 2012-11-15 17:00:00 | |
| 1 2012-11-15 10:00:00 2012-11-14 18:00:00 2012-11-15 18:00:00 | |
| 1 2012-11-15 11:00:00 2012-11-14 19:00:00 2012-11-15 19:00:00 | |
| 1 2012-11-15 12:00:00 2012-11-14 20:00:00 2012-11-15 20:00:00 | |
| 1 2012-11-15 13:00:00 2012-11-14 21:00:00 2012-11-15 21:00:00 | |
| 1 2012-11-15 14:00:00 2012-11-14 22:00:00 2012-11-15 22:00:00 | |
| 1 2012-11-15 15:00:00 2012-11-14 23:00:00 2012-11-15 23:00:00 | |
| 1 2012-11-15 16:00:00 2012-11-15 00:00:00 2012-11-16 00:00:00 | |
| 1 2012-11-15 17:00:00 2012-11-15 01:00:00 2012-11-16 01:00:00 | |
| 1 2012-11-15 18:00:00 2012-11-15 02:00:00 2012-11-16 02:00:00 | |
| 1 2012-11-15 19:00:00 2012-11-15 03:00:00 2012-11-16 03:00:00 | |
| 1 2012-11-15 20:00:00 2012-11-15 04:00:00 2012-11-16 04:00:00 | |
| 1 2012-11-15 21:00:00 2012-11-15 05:00:00 2012-11-16 05:00:00 | |
| 1 2012-11-15 22:00:00 2012-11-15 06:00:00 2012-11-16 06:00:00 | |
| 1 2012-11-15 23:00:00 2012-11-15 07:00:00 2012-11-16 07:00:00 | |
| 24 rows selected. | |
| */ | |
| select * from events; | |
| /* | |
| ID TS | |
| ---------- ------------------- | |
| 106 2012-11-14 00:10:00 | |
| 182 2012-11-14 00:20:00 | |
| 123 2012-11-14 00:30:00 | |
| 121 2012-11-14 00:40:00 | |
| 137 2012-11-14 00:50:00 | |
| 106 2012-11-14 01:00:00 | |
| 146 2012-11-14 01:10:00 | |
| 195 2012-11-14 01:20:00 | |
| 194 2012-11-14 01:30:00 | |
| 181 2012-11-14 01:40:00 | |
| 145 2012-11-14 01:50:00 | |
| 126 2012-11-14 02:00:00 | |
| 149 2012-11-14 02:10:00 | |
| 176 2012-11-14 02:20:00 | |
| 182 2012-11-14 02:30:00 | |
| 147 2012-11-14 02:40:00 | |
| 134 2012-11-14 02:50:00 | |
| 117 2012-11-14 03:00:00 | |
| 193 2012-11-14 03:10:00 | |
| 116 2012-11-14 03:20:00 | |
| 175 2012-11-14 03:30:00 | |
| 172 2012-11-14 03:40:00 | |
| 112 2012-11-14 03:50:00 | |
| 161 2012-11-14 04:00:00 | |
| 112 2012-11-14 04:10:00 | |
| 103 2012-11-14 04:20:00 | |
| 138 2012-11-14 04:30:00 | |
| 195 2012-11-14 04:40:00 | |
| 125 2012-11-14 04:50:00 | |
| 170 2012-11-14 05:00:00 | |
| 100 2012-11-14 05:10:00 | |
| 186 2012-11-14 05:20:00 | |
| 199 2012-11-14 05:30:00 | |
| 102 2012-11-14 05:40:00 | |
| 112 2012-11-14 05:50:00 | |
| 109 2012-11-14 06:00:00 | |
| 140 2012-11-14 06:10:00 | |
| 113 2012-11-14 06:20:00 | |
| 109 2012-11-14 06:30:00 | |
| 121 2012-11-14 06:40:00 | |
| 181 2012-11-14 06:50:00 | |
| 137 2012-11-14 07:00:00 | |
| 136 2012-11-14 07:10:00 | |
| 132 2012-11-14 07:20:00 | |
| 176 2012-11-14 07:30:00 | |
| 111 2012-11-14 07:40:00 | |
| 167 2012-11-14 07:50:00 | |
| 134 2012-11-14 08:00:00 | |
| 112 2012-11-14 08:10:00 | |
| 153 2012-11-14 08:20:00 | |
| 167 2012-11-14 08:30:00 | |
| 192 2012-11-14 08:40:00 | |
| 139 2012-11-14 08:50:00 | |
| 108 2012-11-14 09:00:00 | |
| 105 2012-11-14 09:10:00 | |
| 118 2012-11-14 09:20:00 | |
| 186 2012-11-14 09:30:00 | |
| 161 2012-11-14 09:40:00 | |
| 116 2012-11-14 09:50:00 | |
| 162 2012-11-14 10:00:00 | |
| 177 2012-11-14 10:10:00 | |
| 146 2012-11-14 10:20:00 | |
| 181 2012-11-14 10:30:00 | |
| 194 2012-11-14 10:40:00 | |
| 184 2012-11-14 10:50:00 | |
| 158 2012-11-14 11:00:00 | |
| 136 2012-11-14 11:10:00 | |
| 189 2012-11-14 11:20:00 | |
| 189 2012-11-14 11:30:00 | |
| 191 2012-11-14 11:40:00 | |
| 168 2012-11-14 11:50:00 | |
| 116 2012-11-14 12:00:00 | |
| 155 2012-11-14 12:10:00 | |
| 129 2012-11-14 12:20:00 | |
| 148 2012-11-14 12:30:00 | |
| 151 2012-11-14 12:40:00 | |
| 183 2012-11-14 12:50:00 | |
| 180 2012-11-14 13:00:00 | |
| 195 2012-11-14 13:10:00 | |
| 124 2012-11-14 13:20:00 | |
| 156 2012-11-14 13:30:00 | |
| 105 2012-11-14 13:40:00 | |
| 187 2012-11-14 13:50:00 | |
| 165 2012-11-14 14:00:00 | |
| 179 2012-11-14 14:10:00 | |
| 106 2012-11-14 14:20:00 | |
| 104 2012-11-14 14:30:00 | |
| 185 2012-11-14 14:40:00 | |
| 163 2012-11-14 14:50:00 | |
| 129 2012-11-14 15:00:00 | |
| 171 2012-11-14 15:10:00 | |
| 117 2012-11-14 15:20:00 | |
| 159 2012-11-14 15:30:00 | |
| 191 2012-11-14 15:40:00 | |
| 115 2012-11-14 15:50:00 | |
| 165 2012-11-14 16:00:00 | |
| 196 2012-11-14 16:10:00 | |
| 172 2012-11-14 16:20:00 | |
| 122 2012-11-14 16:30:00 | |
| 165 2012-11-14 16:40:00 | |
| 103 2012-11-14 16:50:00 | |
| 136 2012-11-14 17:00:00 | |
| 150 2012-11-14 17:10:00 | |
| 167 2012-11-14 17:20:00 | |
| 182 2012-11-14 17:30:00 | |
| 115 2012-11-14 17:40:00 | |
| 144 2012-11-14 17:50:00 | |
| 123 2012-11-14 18:00:00 | |
| 189 2012-11-14 18:10:00 | |
| 101 2012-11-14 18:20:00 | |
| 142 2012-11-14 18:30:00 | |
| 142 2012-11-14 18:40:00 | |
| 166 2012-11-14 18:50:00 | |
| 104 2012-11-14 19:00:00 | |
| 127 2012-11-14 19:10:00 | |
| 156 2012-11-14 19:20:00 | |
| 153 2012-11-14 19:30:00 | |
| 186 2012-11-14 19:40:00 | |
| 179 2012-11-14 19:50:00 | |
| 147 2012-11-14 20:00:00 | |
| 188 2012-11-14 20:10:00 | |
| 107 2012-11-14 20:20:00 | |
| 107 2012-11-14 20:30:00 | |
| 149 2012-11-14 20:40:00 | |
| 183 2012-11-14 20:50:00 | |
| 184 2012-11-14 21:00:00 | |
| 182 2012-11-14 21:10:00 | |
| 151 2012-11-14 21:20:00 | |
| 102 2012-11-14 21:30:00 | |
| 171 2012-11-14 21:40:00 | |
| 117 2012-11-14 21:50:00 | |
| 186 2012-11-14 22:00:00 | |
| 116 2012-11-14 22:10:00 | |
| 145 2012-11-14 22:20:00 | |
| 191 2012-11-14 22:30:00 | |
| 139 2012-11-14 22:40:00 | |
| 121 2012-11-14 22:50:00 | |
| 131 2012-11-14 23:00:00 | |
| 188 2012-11-14 23:10:00 | |
| 168 2012-11-14 23:20:00 | |
| 108 2012-11-14 23:30:00 | |
| 147 2012-11-14 23:40:00 | |
| 127 2012-11-14 23:50:00 | |
| 129 2012-11-15 00:00:00 | |
| 134 2012-11-15 00:10:00 | |
| 198 2012-11-15 00:20:00 | |
| 173 2012-11-15 00:30:00 | |
| 113 2012-11-15 00:40:00 | |
| 177 2012-11-15 00:50:00 | |
| 195 2012-11-15 01:00:00 | |
| 113 2012-11-15 01:10:00 | |
| 184 2012-11-15 01:20:00 | |
| 179 2012-11-15 01:30:00 | |
| 129 2012-11-15 01:40:00 | |
| 115 2012-11-15 01:50:00 | |
| 186 2012-11-15 02:00:00 | |
| 121 2012-11-15 02:10:00 | |
| 132 2012-11-15 02:20:00 | |
| 119 2012-11-15 02:30:00 | |
| 184 2012-11-15 02:40:00 | |
| 187 2012-11-15 02:50:00 | |
| 161 2012-11-15 03:00:00 | |
| 110 2012-11-15 03:10:00 | |
| 105 2012-11-15 03:20:00 | |
| 147 2012-11-15 03:30:00 | |
| 134 2012-11-15 03:40:00 | |
| 181 2012-11-15 03:50:00 | |
| 187 2012-11-15 04:00:00 | |
| 136 2012-11-15 04:10:00 | |
| 115 2012-11-15 04:20:00 | |
| 124 2012-11-15 04:30:00 | |
| 161 2012-11-15 04:40:00 | |
| 133 2012-11-15 04:50:00 | |
| 107 2012-11-15 05:00:00 | |
| 177 2012-11-15 05:10:00 | |
| 122 2012-11-15 05:20:00 | |
| 106 2012-11-15 05:30:00 | |
| 180 2012-11-15 05:40:00 | |
| 162 2012-11-15 05:50:00 | |
| 160 2012-11-15 06:00:00 | |
| 180 2012-11-15 06:10:00 | |
| 195 2012-11-15 06:20:00 | |
| 135 2012-11-15 06:30:00 | |
| 181 2012-11-15 06:40:00 | |
| 100 2012-11-15 06:50:00 | |
| 132 2012-11-15 07:00:00 | |
| 173 2012-11-15 07:10:00 | |
| 138 2012-11-15 07:20:00 | |
| 178 2012-11-15 07:30:00 | |
| 110 2012-11-15 07:40:00 | |
| 124 2012-11-15 07:50:00 | |
| 108 2012-11-15 08:00:00 | |
| 193 2012-11-15 08:10:00 | |
| 198 2012-11-15 08:20:00 | |
| 174 2012-11-15 08:30:00 | |
| 155 2012-11-15 08:40:00 | |
| 182 2012-11-15 08:50:00 | |
| 109 2012-11-15 09:00:00 | |
| 117 2012-11-15 09:10:00 | |
| 170 2012-11-15 09:20:00 | |
| 114 2012-11-15 09:30:00 | |
| 198 2012-11-15 09:40:00 | |
| 174 2012-11-15 09:50:00 | |
| 111 2012-11-15 10:00:00 | |
| 103 2012-11-15 10:10:00 | |
| 190 2012-11-15 10:20:00 | |
| 106 2012-11-15 10:30:00 | |
| 186 2012-11-15 10:40:00 | |
| 110 2012-11-15 10:50:00 | |
| 177 2012-11-15 11:00:00 | |
| 147 2012-11-15 11:10:00 | |
| 101 2012-11-15 11:20:00 | |
| 128 2012-11-15 11:30:00 | |
| 154 2012-11-15 11:40:00 | |
| 166 2012-11-15 11:50:00 | |
| 187 2012-11-15 12:00:00 | |
| 193 2012-11-15 12:10:00 | |
| 183 2012-11-15 12:20:00 | |
| 144 2012-11-15 12:30:00 | |
| 125 2012-11-15 12:40:00 | |
| 145 2012-11-15 12:50:00 | |
| 105 2012-11-15 13:00:00 | |
| 195 2012-11-15 13:10:00 | |
| 129 2012-11-15 13:20:00 | |
| 114 2012-11-15 13:30:00 | |
| 198 2012-11-15 13:40:00 | |
| 116 2012-11-15 13:50:00 | |
| 116 2012-11-15 14:00:00 | |
| 116 2012-11-15 14:10:00 | |
| 194 2012-11-15 14:20:00 | |
| 193 2012-11-15 14:30:00 | |
| 120 2012-11-15 14:40:00 | |
| 178 2012-11-15 14:50:00 | |
| 137 2012-11-15 15:00:00 | |
| 172 2012-11-15 15:10:00 | |
| 183 2012-11-15 15:20:00 | |
| 186 2012-11-15 15:30:00 | |
| 142 2012-11-15 15:40:00 | |
| 168 2012-11-15 15:50:00 | |
| 110 2012-11-15 16:00:00 | |
| 110 2012-11-15 16:10:00 | |
| 120 2012-11-15 16:20:00 | |
| 139 2012-11-15 16:30:00 | |
| 106 2012-11-15 16:40:00 | |
| 165 2012-11-15 16:50:00 | |
| 191 2012-11-15 17:00:00 | |
| 196 2012-11-15 17:10:00 | |
| 187 2012-11-15 17:20:00 | |
| 181 2012-11-15 17:30:00 | |
| 118 2012-11-15 17:40:00 | |
| 181 2012-11-15 17:50:00 | |
| 127 2012-11-15 18:00:00 | |
| 114 2012-11-15 18:10:00 | |
| 112 2012-11-15 18:20:00 | |
| 100 2012-11-15 18:30:00 | |
| 129 2012-11-15 18:40:00 | |
| 197 2012-11-15 18:50:00 | |
| 191 2012-11-15 19:00:00 | |
| 127 2012-11-15 19:10:00 | |
| 120 2012-11-15 19:20:00 | |
| 184 2012-11-15 19:30:00 | |
| 199 2012-11-15 19:40:00 | |
| 106 2012-11-15 19:50:00 | |
| 188 2012-11-15 20:00:00 | |
| 115 2012-11-15 20:10:00 | |
| 120 2012-11-15 20:20:00 | |
| 185 2012-11-15 20:30:00 | |
| 114 2012-11-15 20:40:00 | |
| 197 2012-11-15 20:50:00 | |
| 135 2012-11-15 21:00:00 | |
| 198 2012-11-15 21:10:00 | |
| 104 2012-11-15 21:20:00 | |
| 103 2012-11-15 21:30:00 | |
| 183 2012-11-15 21:40:00 | |
| 132 2012-11-15 21:50:00 | |
| 111 2012-11-15 22:00:00 | |
| 196 2012-11-15 22:10:00 | |
| 192 2012-11-15 22:20:00 | |
| 116 2012-11-15 22:30:00 | |
| 196 2012-11-15 22:40:00 | |
| 117 2012-11-15 22:50:00 | |
| 197 2012-11-15 23:00:00 | |
| 144 2012-11-15 23:10:00 | |
| 131 2012-11-15 23:20:00 | |
| 107 2012-11-15 23:30:00 | |
| 193 2012-11-15 23:40:00 | |
| 149 2012-11-15 23:50:00 | |
| 176 2012-11-16 00:00:00 | |
| 129 2012-11-16 00:10:00 | |
| 100 2012-11-16 00:20:00 | |
| 103 2012-11-16 00:30:00 | |
| 170 2012-11-16 00:40:00 | |
| 142 2012-11-16 00:50:00 | |
| 175 2012-11-16 01:00:00 | |
| 199 2012-11-16 01:10:00 | |
| 125 2012-11-16 01:20:00 | |
| 140 2012-11-16 01:30:00 | |
| 124 2012-11-16 01:40:00 | |
| 121 2012-11-16 01:50:00 | |
| 162 2012-11-16 02:00:00 | |
| 126 2012-11-16 02:10:00 | |
| 195 2012-11-16 02:20:00 | |
| 191 2012-11-16 02:30:00 | |
| 185 2012-11-16 02:40:00 | |
| 102 2012-11-16 02:50:00 | |
| 113 2012-11-16 03:00:00 | |
| 139 2012-11-16 03:10:00 | |
| 111 2012-11-16 03:20:00 | |
| 105 2012-11-16 03:30:00 | |
| 117 2012-11-16 03:40:00 | |
| 125 2012-11-16 03:50:00 | |
| 114 2012-11-16 04:00:00 | |
| 189 2012-11-16 04:10:00 | |
| 171 2012-11-16 04:20:00 | |
| 151 2012-11-16 04:30:00 | |
| 192 2012-11-16 04:40:00 | |
| 193 2012-11-16 04:50:00 | |
| 156 2012-11-16 05:00:00 | |
| 165 2012-11-16 05:10:00 | |
| 144 2012-11-16 05:20:00 | |
| 120 2012-11-16 05:30:00 | |
| 188 2012-11-16 05:40:00 | |
| 185 2012-11-16 05:50:00 | |
| 139 2012-11-16 06:00:00 | |
| 110 2012-11-16 06:10:00 | |
| 198 2012-11-16 06:20:00 | |
| 130 2012-11-16 06:30:00 | |
| 144 2012-11-16 06:40:00 | |
| 108 2012-11-16 06:50:00 | |
| 153 2012-11-16 07:00:00 | |
| 174 2012-11-16 07:10:00 | |
| 122 2012-11-16 07:20:00 | |
| 187 2012-11-16 07:30:00 | |
| 108 2012-11-16 07:40:00 | |
| 182 2012-11-16 07:50:00 | |
| 119 2012-11-16 08:00:00 | |
| 111 2012-11-16 08:10:00 | |
| 183 2012-11-16 08:20:00 | |
| 143 2012-11-16 08:30:00 | |
| 112 2012-11-16 08:40:00 | |
| 111 2012-11-16 08:50:00 | |
| 175 2012-11-16 09:00:00 | |
| 190 2012-11-16 09:10:00 | |
| 118 2012-11-16 09:20:00 | |
| 172 2012-11-16 09:30:00 | |
| 155 2012-11-16 09:40:00 | |
| 163 2012-11-16 09:50:00 | |
| 135 2012-11-16 10:00:00 | |
| 132 2012-11-16 10:10:00 | |
| 164 2012-11-16 10:20:00 | |
| 350 rows selected. | |
| */ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment