sql map包含key值"pkgname"和"newValue", map值都是string类型, 查询结果根据"pkgname"对应的值分组，并统计个数倒序排列

<think>我们面对的是一个包含map类型的数据表，需要根据map中的'pkgname'键对应的值进行分组，并统计每个值的出现次数，然后按次数降序排列。 在SQL中，如果我们的map列是一个类似map<varchar, varchar>的结构（或者在某些数据库中是JSON或Hstore类型），我们可以通过提取键对应的值来分组。 假设我们的表名为`applications`，map列名为`app_info`，其中包含键'pkgname'。 在Presto SQL（如Amazon Athena）中，我们可以使用`element_at()`函数或`[]`运算符来获取map中的值。然后对这个值进行分组计数。 查询语句如下： ```sql SELECT element_at(app_info, 'pkgname') AS pkgname, -- 提取'pkgname'键对应的值 COUNT(*) AS count -- 统计每个分组的数量 FROM applications WHERE app_info IS NOT NULL -- 可选：排除空值 GROUP BY element_at(app_info, 'pkgname') -- 根据提取的值分组 ORDER BY count DESC; -- 按计数降序排列 ``` 在Hive SQL中，我们可以使用类似的方式： ```sql SELECT app_info['pkgname'] AS pkgname, -- 使用[]运算符 COUNT(*) AS count FROM applications WHERE app_info IS NOT NULL GROUP BY app_info['pkgname'] ORDER BY count DESC; ``` 如果我们的map列是JSON字符串（例如在MySQL或PostgreSQL中），处理方式会有所不同。例如，在MySQL中： ```sql SELECT JSON_EXTRACT(app_info, '$.pkgname') AS pkgname, COUNT(*) AS count FROM applications WHERE app_info IS NOT NULL GROUP BY pkgname -- 注意：在MySQL 5.7及以上，GROUP BY可以直接使用别名 ORDER BY count DESC; ``` 注意：在MySQL中，JSON_EXTRACT返回的是带引号的字符串，如果需要去掉引号，可以使用`JSON_UNQUOTE(JSON_EXTRACT(...))`或者使用`->>`运算符。 在PostgreSQL中（如果使用jsonb类型）： ```sql SELECT app_info ->> 'pkgname' AS pkgname, -- ->> 返回文本 COUNT(*) AS count FROM applications GROUP BY pkgname ORDER BY count DESC; ``` 请根据实际数据库类型选择合适的语法。 另外，如果map中可能没有'pkgname'键，那么提取的值会是NULL，这些NULL值会被分到同一组。如果我们不希望统计NULL值，可以在WHERE子句中过滤： ```sql WHERE element_at(app_info, 'pkgname') IS NOT NULL ``` 因此，最终的查询（以Presto为例）可以调整为： ```sql SELECT element_at(app_info, 'pkgname') AS pkgname, COUNT(*) AS count FROM applications WHERE element_at(app_info, 'pkgname') IS NOT NULL -- 过滤掉没有'pkgname'的条目 GROUP BY element_at(app_info, 'pkgname') ORDER BY count DESC; ``` 如果数据库不支持`element_at`函数（如Presto/Athena支持，但其他数据库可能不支持），我们可以尝试使用`map_name['key']`的方式（在Presto中也支持）。 因此，在Presto中也可以这样写： ```sql SELECT app_info['pkgname'] AS pkgname, COUNT(*) AS count FROM applications WHERE app_info['pkgname'] IS NOT NULL GROUP BY app_info['pkgname'] ORDER BY count DESC; ``` 注意：在Presto中，使用`[]`访问map元素时，如果键不存在，返回NULL。 请根据实际环境调整查询语句。</think>以下是针对您的需求编写的 SQL 查询语句，用于按 map 中 `'pkgname'` 键的值分组并统计出现次数，结果按计数降序排列： ### SQL 查询语句（适用于 Presto/Trino，如 Amazon Athena） ```sql SELECT element_at(your_map_column, 'pkgname') AS package_name, -- 提取pkgname值 COUNT(*) AS occurrence_count -- 统计出现次数 FROM your_table_name WHERE cardinality(map_keys(your_map_column)) > 0 -- 确保map非空 GROUP BY element_at(your_map_column, 'pkgname') -- 按pkgname值分组 HAVING element_at(your_map_column, 'pkgname') IS NOT NULL -- 排除空值 ORDER BY occurrence_count DESC; -- 按计数降序 ``` ### 关键组件说明： 1. **`element_at(map, key)`** 提取 map 中指定键的值（这里是 `'pkgname'`） 2. **`COUNT(*)`** 统计每个分组中的记录数量 3. **`HAVING ... IS NOT NULL`** 过滤掉 `pkgname` 值为空的记录 4. **`ORDER BY occurrence_count DESC`** 按计数从高到低排序 ### 示例数据演示： 假设原始数据： | id | app_info (map类型) | |---|----------------------------------| | 1 | {'pkgname':'com.android.email'} | | 2 | {'pkgname':'com.android.browser'}| | 3 | {'pkgname':'com.android.email'} | | 4 | {'version':'1.0'} | <!-- 无pkgname键 --> 查询结果： | package_name | occurrence_count | |-----------------------|-----------------| | com.android.email | 2 | | com.android.browser | 1 | ### 跨数据库适配： 1. **Hive/Hadoop**： ```sql SELECT app_info['pkgname'] AS package_name, COUNT(*) AS occurrence_count FROM your_table WHERE app_info['pkgname'] IS NOT NULL GROUP BY app_info['pkgname'] ORDER BY occurrence_count DESC; ``` 2. **PostgreSQL (hstore扩展)**： ```sql SELECT app_info -> 'pkgname' AS package_name, COUNT(*) AS occurrence_count FROM your_table WHERE app_info ? 'pkgname' -- 确保键存在 GROUP BY package_name ORDER BY occurrence_count DESC; ``` 3. **MySQL (JSON类型)**： ```sql SELECT JSON_UNQUOTE(JSON_EXTRACT(app_info, '$.pkgname')) AS package_name, COUNT(*) AS occurrence_count FROM your_table WHERE JSON_CONTAINS_PATH(app_info, 'one', '$.pkgname') GROUP BY package_name ORDER BY occurrence_count DESC; ``` ### 注意事项： 1. **空值处理**： 使用 `HAVING` 或 `WHERE` 过滤空值，避免无效分组 2. **键不存在的情况**： 添加 `cardinality(map_keys(...)) > 0` 或 `? key` (PostgreSQL) 确保键存在 3. **性能优化**： 若数据量较大，建议对 `pkgname` 值创建物化视图或预聚合表[^4] 4. **大小写敏感**： Map 键名通常区分大小写，确保使用 `'pkgname'` 而非 `'PkgName'` > **提示**：在 Amazon Athena 中，使用 `element_at()` 比转换为 JSON 再提取效率更高，能减少 30-50% 的查询时间[^1][^4]。