> grep http: current.csv | wc -l
54679
So over 5% of the top 1m sites still don't use HTTPS.
54679
So over 5% of the top 1m sites still don't use HTTPS.
https://play.clickhouse.com/play?user=play#U0VMRUNUIGZsb29yK...
SELECT
floor(log10(rank)) AS r,
count() AS total,
sum(log LIKE '%TLS%') AS tls,
round(tls / total, 2) AS ratio,
anyIf(domain, log NOT LIKE '%TLS%')
FROM minicrawl
WHERE log LIKE '%Content-Length:%'
GROUP BY r
ORDER BY r
┌─r─┬───total─┬─────tls─┬─ratio─┬─anyIf(domain, notLike(log, '%TLS%'))─┐
│ 0 │ 6 │ 6 │ 1 │ │
│ 1 │ 61 │ 58 │ 0.95 │ baidu.com │
│ 2 │ 599 │ 562 │ 0.94 │ google.cn │
│ 3 │ 5591 │ 5057 │ 0.9 │ volganet.ru │
│ 4 │ 51279 │ 44291 │ 0.86 │ furbo.co │
│ 5 │ 476181 │ 361910 │ 0.76 │ funygold.com │
│ 6 │ 3797023 │ 2927052 │ 0.77 │ funyo.vip │
└───┴─────────┴─────────┴───────┴──────────────────────────────────────┘
7 rows in set. Elapsed: 0.844 sec. Processed 7.59 million rows, 43.74 GB (8.99 million rows/s., 51.83 GB/s.)Download it as:
curl https://clickhouse.com/ | sh
Connect to the demo service: clickhouse-client --host play.clickhouse.com --user play --secure grep -o -E "://.*?," current.csv | sort | uniq -c | grep -v "1 ://" | wc -l
8310