从SQL表创建键-值对
我最近遇到了一个问题，我在一个专栏中有许多网站的名称(有重复)，我必须找出同时以http和https存在的域名(例如：https://www.google.com和http://www.google.com)。我提出的问题是-
`SELECT distinct SPLIT(origin,"://")[OFFSET(1)] as domain
FROM "chrome-ux-report.chrome_ux_report.201710" x
WHERE SPLIT(x.origin,"://")[OFFSET(0)] = "http"
and SPLIT(x.origin,"://")[OFFSET(1)] in
(SELECT SPLIT(y.origin,"://")[OFFSET(1)]
FROM "chrome-ux-report.chrome_ux_report.201710" y
WHERE SPLIT(y.origin,"://")[OFFSET(0)] = "https" )
ORDER BY domain`这个查询需要O(n^2)个时间。了解python之后，我不禁想到了一个解决方案，在这个解决方案中，我可以使用domain作为键，使用http和https的存在值作为值，例如dict'www.google.com‘= 1,1，或者在sql中-
Domain http https
www.google.com 1 1这需要O(n) time.Any知道我如何做到这一点吗？提前谢谢。
回答 3
Stack Overflow用户
发布于 2018-01-19 06:24:05
使用公用表表达式来预选数据应该会大大减少O(n^2)。不是O(n)，但更接近O(N)。
WITH cte AS (
SELECT DISTINCT origin
FROM "chrome-ux-report.chrome_ux_report.201710" x
WHERE SPLIT(x.origin,"://")[OFFSET(0)] IN ("http", "https")
)
SELECT DISTINCT SPLIT(origin,"://")[OFFSET(1)] AS domain
FROM cte
WHERE SPLIT(cte.origin,"://")[OFFSET(0)] = "http"
AND SPLIT(cte.origin,"://")[OFFSET(1)] IN (
SELECT SPLIT(cte2.origin,"://")[OFFSET(1)]
FROM cte AS cte2
WHERE SPLIT(cte2.origin,"://")[OFFSET(0)] = "https"
)
ORDER BY domainStack Overflow用户
发布于 2018-01-19 06:39:59
嗯，这是一个有趣的问题；我就抛出我的2分钱吧。下面是我在BigQuery中解决这个问题的方法：
WITH data AS(
SELECT 'http://google.com.br' AS origin UNION ALL
SELECT 'https://google.com.br' AS origin UNION ALL
SELECT 'https://www.google.com.br' UNION ALL
SELECT 'http://domain1' UNION ALL
SELECT 'https://domain2'
)
SELECT
REGEXP_EXTRACT(origin, r'://(.*)') AS domain,
MAX(IF(REGEXP_CONTAINS(origin, r'^http[^s]'), TRUE, FALSE)) http,
MAX(IF(REGEXP_CONTAINS(origin, r'^https'), TRUE, FALSE)) https
FROM data
GROUP BY 1结果：
Row domain http https
1 www.google.com.br false true
2 domain2 false true
3 google.com.br true true
4 domain1 true false我认为这个查询的BigO比n^2低，但可能比n大:在MySQL中，一个索引列AFAIK可以在log(n) (假设是b索引树)中查找行，并且由于这种情况在这个查询中发生了n次，所以最终结果是nlog(n)。
(也许可以使用Hash Map到达O(n)，但我不知道最终结果是否会是n)
另一方面，BigQuery没有索引；正如您在这篇post (令人惊叹的阅读)中所看到的那样，它有不同的数据管理策略。
不过，我想BigQuery可能无法联系到O(n)来完成这项任务。
Stack Overflow用户
发布于 2018-01-19 06:24:54
像这样的怎么样？通过这种方式，子查询之间几乎没有太多的相关性。
Select sum(if(origin like 'http://%', 1,0 ) as http,
sum(if(origin like 'https://%', 1,0 ) as https,
Replace(Replace (origin,'http://',''),'https://','') as domain
Group by domain假设你真的想要MySQL，这似乎是有问题的。
https://stackoverflow.com/questions/48331099
复制相似问题
腾讯云开发者
