hivesql实现拆分[{"group":"tx_after_uid_key","writeNum":7219368}, {"group":"bankcard_recommend_user_feature_v1","writeNum":318984649}]
时间: 2024-06-29 08:01:11 浏览: 165
在Hive SQL中,如果你想要拆分这个JSON数组,通常是通过`UNNEST`函数来展开JSON对象,然后你可以使用`GROUP BY`对每个`group`字段进行分组并计算`writeNum`的总计或平均值。这里是一个基本的示例,假设你有一个名为`json_data`的列,其中包含你提供的JSON数组:
```sql
CREATE TEMPORARY TABLE temp_table AS
SELECT
json_data.group,
SUM(json_data.writeNum) as total_write_num
FROM
(SELECT UNNEST([json_data]) as json_data FROM your_table_name) t
GROUP BY
json_data.group;
```
这将创建一个临时表`temp_table`,其中包含每个`group`对应的`writeNum`总和。
如果你想获取每个`group`的写入次数,你可以直接使用`writeNum`字段,无需计算总和,只需去掉`SUM`关键字:
```sql
CREATE TEMPORARY TABLE temp_table AS
SELECT
json_data.group,
json_data.writeNum
FROM
(SELECT UNNEST([json_data]) as json_data FROM your_table_name) t
GROUP BY
json_data.group, json_data.writeNum;
```
如果你还有其他需求,比如查询特定`group`的写入次数,可以添加`HAVING`子句:
```sql
CREATE TEMPORARY TABLE temp_table AS
SELECT
json_data.group,
json_data.writeNum
FROM
(SELECT UNNEST([json_data]) as json_data FROM your_table_name) t
GROUP BY
json_data.group, json_data.writeNum
HAVING
json_data.group = 'tx_after_uid_key';
```
阅读全文