从sql表创建键值对

xmq68pz9  于 2021-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(251)

我最近遇到了一个问题,我在一个专栏中列出了许多网站的名称(有重复),我必须找出以http和https形式存在的域(例如:https://www.google.com and 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之后,我不禁想到了一个解决方案,在这个解决方案中,我可以构建一个新表,其中域作为键,http和https作为值,例如dict['www.google.com']=[1,1]或在sql中-

Domain     http     https
        www.google.com      1         1

你知道我怎么做吗?提前谢谢。

ezykj2lf

ezykj2lf1#

使用公共表表达式来预选数据会减少很多o(n^2)。不是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 domain
nukf8bse

nukf8bse2#

像这样的怎么样?这样子查询之间就没有那么多相关性了。

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,这似乎有问题。

ssgvzors

ssgvzors3#

嗯,这是个有趣的问题;我就扔我的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) .
(也许可以使用哈希Map O(n) 但我不知道最终结果会是什么 n )
另一方面,bigquery没有索引;正如你在这篇文章(惊人的阅读)中看到的,它确实有不同的数据管理策略。
尽管如此,bigquery可能无法访问 O(n) 我想是为了这个任务。

相关问题