我如何在bigquery中搜索表达式并将它们分组(即使它们被分号弄乱了)?
数据库示例:
:Adidas
Adidas
Adidas;
null
adidas
7up
7UP
7UP;
:7UP
null
我想将它们分组并计数。我想得到这个结果:
adidas 4
7up 4
null 2
到目前为止,组合没有帮助,我在Excel中完成了工作,但一切都很快。
首先,您需要规范化文本以仅保留有效词。下面的正则表达式只是一个简单的表达式,您需要匹配并扩展您的逻辑。
SELECT normalized,
count(1) AS c
FROM
(SELECT label,
lower(REGEXP_EXTRACT(label,r'[[:punct:]]?([[:^punct:]]*)')) AS normalized
FROM
(SELECT string(':Adidas') AS label),
(SELECT string('Adidas') AS label),
(SELECT string('Adidas;') AS label),
(SELECT string(NULL) AS label),
(SELECT string('adidas') AS label),
(SELECT string('7up') AS label),
(SELECT string('7UP') AS label),
(SELECT string('7UP;') AS label),
(SELECT string(':7UP') AS label),
(SELECT string(NULL) AS label),)
GROUP BY normalized
ORDER BY c DESC
输出:
+-----+------------+---+---+
| Row | normalized | c | |
+-----+------------+---+---+
| 1 | adidas | 4 | |
| 2 | 7up | 4 | |
| 3 | null | 2 | |
+-----+------------+---+---+
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句