Skip to content

Instantly share code, notes, and snippets.

@nmreadelf

nmreadelf/4x origin

Last active May 5, 2023 13:57
Show Gist options
  • Save nmreadelf/ee81d0f375b06f9f4dddc779976e9c15 to your computer and use it in GitHub Desktop.
Save nmreadelf/ee81d0f375b06f9f4dddc779976e9c15 to your computer and use it in GitHub Desktop.
Download ZIP
2023-05-05 chdb 0.7.0
Raw
4x origin
SELECT COUNT(*) FROM file("hits_*.parquet", Parquet);
0.4429148039999973
0.05523164400000269
0.05387431600001946
SELECT COUNT(*) FROM file("hits_*.parquet", Parquet) WHERE AdvEngineID <> 0;
0.4565690250000216
0.06102334700000256
0.0607522119999544
SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet);
1.3598468980000007
0.12032856700000139
0.1193006040000455
SELECT AVG(UserID) FROM file("hits_*.parquet", Parquet);
0.9612236649999772
0.1929021569999918
0.19267964400000892
SELECT COUNT(DISTINCT UserID) FROM file("hits_*.parquet", Parquet);
1.9829911109999898
1.28030129199999
1.285285282000018
SELECT COUNT(DISTINCT SearchPhrase) FROM file("hits_*.parquet", Parquet);
1.7935391819999609
0.9826381660000152
1.0141566190000049
SELECT MIN(EventDate), MAX(EventDate) FROM file("hits_*.parquet", Parquet);
0.41091510500001505
0.06351907099997334
0.06336690000000544
SELECT AdvEngineID, COUNT(*) FROM file("hits_*.parquet", Parquet) WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
0.45736787700002424
0.07086103400001775
0.06989806999996517
SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) GROUP BY RegionID ORDER BY u DESC LIMIT 10;
1.430184364000013
0.7207346200000302
0.7194937749999895
SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM file("hits_*.parquet", Parquet) GROUP BY RegionID ORDER BY c DESC LIMIT 10;
3.5725714509999875
0.8871110950000229
0.8889150309999536
SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
2.30405809399997
0.3897291979999977
0.38142487100003564
SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
2.29348246699999
0.4307406530000435
0.4320173509999563
SELECT SearchPhrase, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
1.700774100999979
0.8479208649999919
0.8368182500000216
SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
3.5948856439999872
1.2296576100000038
1.248441114000002
SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT 10;
1.729476883000018
0.9478605229999744
0.9708910519999563
SELECT UserID, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID ORDER BY COUNT(*) DESC LIMIT 10;
1.4120810699999993
0.8927785120000067
0.9024119960000121
SELECT UserID, SearchPhrase, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
4.198422257999994
2.099777385999971
2.0570513969999524
SELECT UserID, SearchPhrase, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID, SearchPhrase LIMIT 10;
3.451509723000015
1.3070338380000521
1.3172277590000476
SELECT UserID, extract(minute FROM toDateTime(EventTime)) AS m, SearchPhrase, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
7.465000632999931
4.064454146000003
4.098140042999944
SELECT UserID FROM file("hits_*.parquet", Parquet) WHERE UserID = 435090932899640449;
1.011559492999936
0.19920514399996136
0.1996721409999509
SELECT COUNT(*) FROM file("hits_*.parquet", Parquet) WHERE URL LIKE '%google%';
10.191839989000073
3.43142776600007
3.4344062769999937
SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE URL LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
12.449996616999897
3.825017042000013
4.2142096649999985
SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c, COUNT(DISTINCT UserID) FROM file("hits_*.parquet", Parquet) WHERE Title LIKE '%Google%' AND URL NOT LIKE '%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
23.44108970700006
6.952842314000009
7.031069702000082
SELECT * FROM file("hits_*.parquet", Parquet) WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
55.764992790000065
19.251818794999963
18.668459697000003
SELECT SearchPhrase FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' ORDER BY EventTime LIMIT 10;
3.783942905999993
0.8748886040000343
0.818624403000058
SELECT SearchPhrase FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' ORDER BY SearchPhrase LIMIT 10;
1.4633729979999544
0.7153551100000186
0.7150954050000564
SELECT SearchPhrase FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' ORDER BY EventTime, SearchPhrase LIMIT 10;
3.758744128999979
0.7623701550000987
0.7680595650000441
SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
11.221320278999997
4.055631392999999
4.095629183000028
SELECT REGEXP_REPLACE(Referer, '^https?://(?:www.)?([^/]+)/.*$', '1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM file("hits_*.parquet", Parquet) WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
10.577421466999908
10.193900503999998
10.194105725999975
SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(ResolutionWidth + 18), SUM(ResolutionWidth + 19), SUM(ResolutionWidth + 20), SUM(ResolutionWidth + 21), SUM(ResolutionWidth + 22), SUM(ResolutionWidth + 23), SUM(ResolutionWidth + 24), SUM(ResolutionWidth + 25), SUM(ResolutionWidth + 26), SUM(ResolutionWidth + 27), SUM(ResolutionWidth + 28), SUM(ResolutionWidth + 29), SUM(ResolutionWidth + 30), SUM(ResolutionWidth + 31), SUM(ResolutionWidth + 32), SUM(ResolutionWidth + 33), SUM(ResolutionWidth + 34), SUM(ResolutionWidth + 35), SUM(ResolutionWidth + 36), SUM(ResolutionWidth + 37), SUM(ResolutionWidth + 38), SUM(ResolutionWidth + 39), SUM(ResolutionWidth + 40), SUM(ResolutionWidth + 41), SUM(ResolutionWidth + 42), SUM(ResolutionWidth + 43), SUM(ResolutionWidth + 44), SUM(ResolutionWidth + 45), SUM(ResolutionWidth + 46), SUM(ResolutionWidth + 47), SUM(ResolutionWidth + 48), SUM(ResolutionWidth + 49), SUM(ResolutionWidth + 50), SUM(ResolutionWidth + 51), SUM(ResolutionWidth + 52), SUM(ResolutionWidth + 53), SUM(ResolutionWidth + 54), SUM(ResolutionWidth + 55), SUM(ResolutionWidth + 56), SUM(ResolutionWidth + 57), SUM(ResolutionWidth + 58), SUM(ResolutionWidth + 59), SUM(ResolutionWidth + 60), SUM(ResolutionWidth + 61), SUM(ResolutionWidth + 62), SUM(ResolutionWidth + 63), SUM(ResolutionWidth + 64), SUM(ResolutionWidth + 65), SUM(ResolutionWidth + 66), SUM(ResolutionWidth + 67), SUM(ResolutionWidth + 68), SUM(ResolutionWidth + 69), SUM(ResolutionWidth + 70), SUM(ResolutionWidth + 71), SUM(ResolutionWidth + 72), SUM(ResolutionWidth + 73), SUM(ResolutionWidth + 74), SUM(ResolutionWidth + 75), SUM(ResolutionWidth + 76), SUM(ResolutionWidth + 77), SUM(ResolutionWidth + 78), SUM(ResolutionWidth + 79), SUM(ResolutionWidth + 80), SUM(ResolutionWidth + 81), SUM(ResolutionWidth + 82), SUM(ResolutionWidth + 83), SUM(ResolutionWidth + 84), SUM(ResolutionWidth + 85), SUM(ResolutionWidth + 86), SUM(ResolutionWidth + 87), SUM(ResolutionWidth + 88), SUM(ResolutionWidth + 89) FROM file("hits_*.parquet", Parquet);
2.607676301999959
2.4886430799999744
2.482616906999965
SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
4.272568361000026
1.0336377419999963
1.0426221700000724
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
8.506729790999998
1.4242679059999546
1.4193986099999165
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet) GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
9.663964559999954
5.909482622000041
5.942310636999991
SELECT URL, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) GROUP BY URL ORDER BY c DESC LIMIT 10;
11.492136923999965
5.12505598000007
5.419579022000107
SELECT 1, URL, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) GROUP BY 1, URL ORDER BY c DESC LIMIT 10;
11.444171764999965
5.400908896000033
5.401389182999992
SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3 ORDER BY c DESC LIMIT 10;
1.2777752949999694
1.104499455999985
1.1210199329999568
SELECT URL, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
13.688373542000022
3.502314116999969
3.5483109240000203
SELECT Title, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC LIMIT 10;
12.752169234000007
3.4852519440000833
3.4620014020000554
SELECT URL, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
13.591583440000022
3.524202589999959
3.586513114000013
SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN (SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL AS Dst, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 GROUP BY TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
21.33622246499999
5.870797158000073
5.880134524999903
SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash = 3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
6.021084814999995
0.6955702609999435
0.6989175190000196
SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash = 2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
6.2017966370000295
0.6558101169999873
0.6562119049999637
SELECT DATE_TRUNC('minute', toDateTime(EventTime)) AS M, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-14' AND toDate(EventDate) <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_TRUNC('minute', toDateTime(EventTime)) ORDER BY DATE_TRUNC('minute', toDateTime(EventTime)) LIMIT 10 OFFSET 1000;
4.021110394000061
0.6727561569999807
0.5107686380000587
Raw
4x 500g independ disk
[0.4429148039999973,0.05523164400000269,0.05387431600001946],
[0.4565690250000216,0.06102334700000256,0.0607522119999544],
[1.3598468980000007,0.12032856700000139,0.1193006040000455],
[0.9612236649999772,0.1929021569999918,0.19267964400000892],
[1.9829911109999898,1.28030129199999,1.285285282000018],
[1.7935391819999609,0.9826381660000152,1.0141566190000049],
[0.41091510500001505,0.06351907099997334,0.06336690000000544],
[0.45736787700002424,0.07086103400001775,0.06989806999996517],
[1.430184364000013,0.7207346200000302,0.7194937749999895],
[3.5725714509999875,0.8871110950000229,0.8889150309999536],
[2.30405809399997,0.3897291979999977,0.38142487100003564],
[2.29348246699999,0.4307406530000435,0.4320173509999563],
[1.700774100999979,0.8479208649999919,0.8368182500000216],
[3.5948856439999872,1.2296576100000038,1.248441114000002],
[1.729476883000018,0.9478605229999744,0.9708910519999563],
[1.4120810699999993,0.8927785120000067,0.9024119960000121],
[4.198422257999994,2.099777385999971,2.0570513969999524],
[3.451509723000015,1.3070338380000521,1.3172277590000476],
[7.465000632999931,4.064454146000003,4.098140042999944],
[1.011559492999936,0.19920514399996136,0.1996721409999509],
[10.191839989000073,3.43142776600007,3.4344062769999937],
[12.449996616999897,3.825017042000013,4.2142096649999985],
[23.44108970700006,6.952842314000009,7.031069702000082],
[55.764992790000065,19.251818794999963,18.668459697000003],
[3.783942905999993,0.8748886040000343,0.818624403000058],
[1.4633729979999544,0.7153551100000186,0.7150954050000564],
[3.758744128999979,0.7623701550000987,0.7680595650000441],
[11.221320278999997,4.055631392999999,4.095629183000028],
[10.577421466999908,10.193900503999998,10.194105725999975],
[2.607676301999959,2.4886430799999744,2.482616906999965],
[4.272568361000026,1.0336377419999963,1.0426221700000724],
[8.506729790999998,1.4242679059999546,1.4193986099999165],
[9.663964559999954,5.909482622000041,5.942310636999991],
[11.492136923999965,5.12505598000007,5.419579022000107],
[11.444171764999965,5.400908896000033,5.401389182999992],
[1.2777752949999694,1.104499455999985,1.1210199329999568],
[13.688373542000022,3.502314116999969,3.5483109240000203],
[12.752169234000007,3.4852519440000833,3.4620014020000554],
[13.591583440000022,3.524202589999959,3.586513114000013],
[21.33622246499999,5.870797158000073,5.880134524999903],
[6.021084814999995,0.6955702609999435,0.6989175190000196],
[6.2017966370000295,0.6558101169999873,0.6562119049999637],
[4.021110394000061,0.6727561569999807,0.5107686380000587]
Raw
meta origin
SELECT COUNT(*) FROM file("hits_*.parquet", Parquet);
0.49566732299999217
0.03513280600000712
0.03171252999999297
SELECT COUNT(*) FROM file("hits_*.parquet", Parquet) WHERE AdvEngineID <> 0;
0.47875189299998056
0.0344104909999885
0.031138658999992685
SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet);
1.3694916039999896
0.044922430000013946
0.04632496700000388
SELECT AVG(UserID) FROM file("hits_*.parquet", Parquet);
1.0058225090000121
0.06778644199999917
0.0604267549999804
SELECT COUNT(DISTINCT UserID) FROM file("hits_*.parquet", Parquet);
2.3731753630000014
1.4985730260000025
1.4825330329999815
SELECT COUNT(DISTINCT SearchPhrase) FROM file("hits_*.parquet", Parquet);
2.0324843930000043
0.761038966000001
0.7666237319999993
SELECT MIN(EventDate), MAX(EventDate) FROM file("hits_*.parquet", Parquet);
0.47298457299999086
0.033007725000004484
0.031206302000015285
SELECT AdvEngineID, COUNT(*) FROM file("hits_*.parquet", Parquet) WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
0.5057313920000013
0.055369079000001875
0.056027591000002985
SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) GROUP BY RegionID ORDER BY u DESC LIMIT 10;
1.4244333030000007
0.3437127170000167
0.32996887500002003
SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM file("hits_*.parquet", Parquet) GROUP BY RegionID ORDER BY c DESC LIMIT 10;
3.590906572999984
0.4236573700000008
0.378383762999988
SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
2.3780129030000126
0.22519701400000258
0.22398933899998497
SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel ORDER BY u DESC LIMIT 10;
2.3785997240000256
0.24335201700000653
0.2374862359999952
SELECT SearchPhrase, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
1.6513588400000003
0.3183119359999864
0.31840762699999914
SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
3.4760940209999944
0.4446768870000142
0.36989677200000415
SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT 10;
1.6953210809999746
0.34144186399998944
0.3202062180000098
SELECT UserID, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID ORDER BY COUNT(*) DESC LIMIT 10;
1.3145988349999698
0.2875412230000052
0.26546957499999735
SELECT UserID, SearchPhrase, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
3.6673165699999686
0.6211193380000282
0.5918964539999934
SELECT UserID, SearchPhrase, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID, SearchPhrase LIMIT 10;
3.5694325219999996
0.5153826240000399
0.47932149400003254
SELECT UserID, extract(minute FROM toDateTime(EventTime)) AS m, SearchPhrase, COUNT(*) FROM file("hits_*.parquet", Parquet) GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
6.097162239999989
1.0900478410000005
1.0259834299999966
SELECT UserID FROM file("hits_*.parquet", Parquet) WHERE UserID = 435090932899640449;
1.046241302999988
0.07678355199999487
0.07197819100002789
SELECT COUNT(*) FROM file("hits_*.parquet", Parquet) WHERE URL LIKE '%google%';
10.059481294000022
1.26308781299997
1.2545825930000092
SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE URL LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
12.423554534999994
1.3116924830000016
1.1115871690000176
SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c, COUNT(DISTINCT UserID) FROM file("hits_*.parquet", Parquet) WHERE Title LIKE '%Google%' AND URL NOT LIKE '%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
23.570250722000026
2.3460872910000035
2.1065517229999955
SELECT * FROM file("hits_*.parquet", Parquet) WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
56.26244652199995
6.519461867999951
5.87129287700003
SELECT SearchPhrase FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' ORDER BY EventTime LIMIT 10;
3.8556964410000205
0.33200872100002243
0.2971470790000126
SELECT SearchPhrase FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' ORDER BY SearchPhrase LIMIT 10;
1.4818769629999906
0.31141265400003704
0.31887213700002803
SELECT SearchPhrase FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' ORDER BY EventTime, SearchPhrase LIMIT 10;
3.7689432270000225
0.2645928549999894
0.24408712899997909
SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
11.205134163000025
1.6793246180000097
1.5914499440000327
SELECT REGEXP_REPLACE(Referer, '^https?://(?:www.)?([^/]+)/.*$', '1') AS k, AVG(length(Referer)) AS l, COUNT(*) AS c, MIN(Referer) FROM file("hits_*.parquet", Parquet) WHERE Referer <> '' GROUP BY k HAVING COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
9.104611247000037
2.187241346999997
2.076418953999962
SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1), SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4), SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7), SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10), SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth + 13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth + 16), SUM(ResolutionWidth + 17), SUM(ResolutionWidth + 18), SUM(ResolutionWidth + 19), SUM(ResolutionWidth + 20), SUM(ResolutionWidth + 21), SUM(ResolutionWidth + 22), SUM(ResolutionWidth + 23), SUM(ResolutionWidth + 24), SUM(ResolutionWidth + 25), SUM(ResolutionWidth + 26), SUM(ResolutionWidth + 27), SUM(ResolutionWidth + 28), SUM(ResolutionWidth + 29), SUM(ResolutionWidth + 30), SUM(ResolutionWidth + 31), SUM(ResolutionWidth + 32), SUM(ResolutionWidth + 33), SUM(ResolutionWidth + 34), SUM(ResolutionWidth + 35), SUM(ResolutionWidth + 36), SUM(ResolutionWidth + 37), SUM(ResolutionWidth + 38), SUM(ResolutionWidth + 39), SUM(ResolutionWidth + 40), SUM(ResolutionWidth + 41), SUM(ResolutionWidth + 42), SUM(ResolutionWidth + 43), SUM(ResolutionWidth + 44), SUM(ResolutionWidth + 45), SUM(ResolutionWidth + 46), SUM(ResolutionWidth + 47), SUM(ResolutionWidth + 48), SUM(ResolutionWidth + 49), SUM(ResolutionWidth + 50), SUM(ResolutionWidth + 51), SUM(ResolutionWidth + 52), SUM(ResolutionWidth + 53), SUM(ResolutionWidth + 54), SUM(ResolutionWidth + 55), SUM(ResolutionWidth + 56), SUM(ResolutionWidth + 57), SUM(ResolutionWidth + 58), SUM(ResolutionWidth + 59), SUM(ResolutionWidth + 60), SUM(ResolutionWidth + 61), SUM(ResolutionWidth + 62), SUM(ResolutionWidth + 63), SUM(ResolutionWidth + 64), SUM(ResolutionWidth + 65), SUM(ResolutionWidth + 66), SUM(ResolutionWidth + 67), SUM(ResolutionWidth + 68), SUM(ResolutionWidth + 69), SUM(ResolutionWidth + 70), SUM(ResolutionWidth + 71), SUM(ResolutionWidth + 72), SUM(ResolutionWidth + 73), SUM(ResolutionWidth + 74), SUM(ResolutionWidth + 75), SUM(ResolutionWidth + 76), SUM(ResolutionWidth + 77), SUM(ResolutionWidth + 78), SUM(ResolutionWidth + 79), SUM(ResolutionWidth + 80), SUM(ResolutionWidth + 81), SUM(ResolutionWidth + 82), SUM(ResolutionWidth + 83), SUM(ResolutionWidth + 84), SUM(ResolutionWidth + 85), SUM(ResolutionWidth + 86), SUM(ResolutionWidth + 87), SUM(ResolutionWidth + 88), SUM(ResolutionWidth + 89) FROM file("hits_*.parquet", Parquet);
1.126220169000021
0.5656948870000065
0.5635434879999934
SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
4.158844481000017
0.3196730440000124
0.329265098999997
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet) WHERE SearchPhrase <> '' GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
8.358581297
0.48456106100002216
0.46074991299997237
SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh), AVG(ResolutionWidth) FROM file("hits_*.parquet", Parquet) GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT 10;
7.253270901999997
1.5093742820000102
1.5104312319999735
SELECT URL, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) GROUP BY URL ORDER BY c DESC LIMIT 10;
10.659070801999974
1.9520910319999984
1.6690703870000334
SELECT 1, URL, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) GROUP BY 1, URL ORDER BY c DESC LIMIT 10;
10.706468514999983
1.7841597189999447
1.6512681080000675
SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3, COUNT(*) AS c FROM file("hits_*.parquet", Parquet) GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3 ORDER BY c DESC LIMIT 10;
0.881176832000051
0.3465713800000003
0.3451940319999949
SELECT URL, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
13.6770029139999
1.1830367769999839
1.143842866
SELECT Title, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND DontCountHits = 0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC LIMIT 10;
12.742314782999983
1.1785206140000355
1.0861657929999637
SELECT URL, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
13.589695342999903
1.2378396589999738
1.0975264209999978
SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN (SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL AS Dst, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 GROUP BY TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
21.43854331199998
1.9222509799999443
1.7227437830000554
SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash = 3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
6.00889249599993
0.21725034899998263
0.2106503170000451
SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-01' AND toDate(EventDate) <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash = 2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
6.2190972840000995
0.2412031769999885
0.22632600999997976
SELECT DATE_TRUNC('minute', toDateTime(EventTime)) AS M, COUNT(*) AS PageViews FROM file("hits_*.parquet", Parquet) WHERE CounterID = 62 AND toDate(EventDate) >= '2013-07-14' AND toDate(EventDate) <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY DATE_TRUNC('minute', toDateTime(EventTime)) ORDER BY DATE_TRUNC('minute', toDateTime(EventTime)) LIMIT 10 OFFSET 1000;
4.039900230999933
0.2524997279999752
0.18860307500006002
Raw
metal 500g independ disk
[0.49566732299999217,0.03513280600000712,0.03171252999999297],
[0.47875189299998056,0.0344104909999885,0.031138658999992685],
[1.3694916039999896,0.044922430000013946,0.04632496700000388],
[1.0058225090000121,0.06778644199999917,0.0604267549999804],
[2.3731753630000014,1.4985730260000025,1.4825330329999815],
[2.0324843930000043,0.761038966000001,0.7666237319999993],
[0.47298457299999086,0.033007725000004484,0.031206302000015285],
[0.5057313920000013,0.055369079000001875,0.056027591000002985],
[1.4244333030000007,0.3437127170000167,0.32996887500002003],
[3.590906572999984,0.4236573700000008,0.378383762999988],
[2.3780129030000126,0.22519701400000258,0.22398933899998497],
[2.3785997240000256,0.24335201700000653,0.2374862359999952],
[1.6513588400000003,0.3183119359999864,0.31840762699999914],
[3.4760940209999944,0.4446768870000142,0.36989677200000415],
[1.6953210809999746,0.34144186399998944,0.3202062180000098],
[1.3145988349999698,0.2875412230000052,0.26546957499999735],
[3.6673165699999686,0.6211193380000282,0.5918964539999934],
[3.5694325219999996,0.5153826240000399,0.47932149400003254],
[6.097162239999989,1.0900478410000005,1.0259834299999966],
[1.046241302999988,0.07678355199999487,0.07197819100002789],
[10.059481294000022,1.26308781299997,1.2545825930000092],
[12.423554534999994,1.3116924830000016,1.1115871690000176],
[23.570250722000026,2.3460872910000035,2.1065517229999955],
[56.26244652199995,6.519461867999951,5.87129287700003],
[3.8556964410000205,0.33200872100002243,0.2971470790000126],
[1.4818769629999906,0.31141265400003704,0.31887213700002803],
[3.7689432270000225,0.2645928549999894,0.24408712899997909],
[11.205134163000025,1.6793246180000097,1.5914499440000327],
[9.104611247000037,2.187241346999997,2.076418953999962],
[1.126220169000021,0.5656948870000065,0.5635434879999934],
[4.158844481000017,0.3196730440000124,0.329265098999997],
[8.358581297,0.48456106100002216,0.46074991299997237],
[7.253270901999997,1.5093742820000102,1.5104312319999735],
[10.659070801999974,1.9520910319999984,1.6690703870000334],
[10.706468514999983,1.7841597189999447,1.6512681080000675],
[0.881176832000051,0.3465713800000003,0.3451940319999949],
[13.6770029139999,1.1830367769999839,1.143842866],
[12.742314782999983,1.1785206140000355,1.0861657929999637],
[13.589695342999903,1.2378396589999738,1.0975264209999978],
[21.43854331199998,1.9222509799999443,1.7227437830000554],
[6.00889249599993,0.21725034899998263,0.2106503170000451],
[6.2190972840000995,0.2412031769999885,0.22632600999997976],
[4.039900230999933,0.2524997279999752,0.18860307500006002]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment