我的数据集中有一列,格式如下:
hashtags
1 [#newyears, #christmas, #christmas]
2 [#easter, #newyears, #fourthofjuly]
3 [#valentines, #christmas, #easter]
我已经设法这样数标签:
SELECT hashtags, (LENGTH(hashtags) - LENGTH(REPLACE(hashtags, ',', '')) + 1) AS hashtag_count
FROM full_data
ORDER BY hashtag_count DESC NULLS LAST
但是我不确定是否可以计算每个标签的出现次数,是否可以用下面的格式返回最流行的标签的计数:
hashtags count
christmas 3
newyears 2
数据类型只是varchar,但是我对如何处理这个有点困惑。任何帮助都将不胜感激!
3条答案
按热度按时间dohp0rv51#
存储这些数据不是一个好主意。这样做很危险,因为我们不知道文本是否总是以这种形式存储。最好将不同的字符串保存在单独的列中。
无论如何,如果你不能改进它,并且必须处理这个结构,我们基本上可以使用
UNNEST
,STRING_TO_ARRAY
和GROUP BY
的组合来分割标签并计算它们。总体思路是这样的:
由于列中的大括号和空格,这将不会产生正确的结果。
因此,我们还可以使用
TRIM
和TRANSLATE
来删除除hashtag之外的所有其他内容。根据您的样本数据,以下构造将产生预期结果:
参见here
但如前所述,这是令人不快的,也是有风险的。
因此,如果可能的话,找出哪些标签是可能的(似乎这些都是特殊的日子),然后为它们创建列或Map表。
也就是说,在列中存储0或1以指示hashtag是否出现,然后对每列的值求和。
wmtdaxz32#
我认为你应该把数组中的所有数据拆分记录,然后用Groupby来计数。
希望它能满足您的要求!
fwzugrvs3#
您可以按以下步骤操作:
结果:
REGEXP_REPLACE
以删除任何特殊字符。string_to_array
以生成数组unnest
将数组扩展为一组行Demo here