如何获取SQL数组中的高频值：统计用户常驻州实现方法

下面分两种常用处理场景给出可直接运行的代码：

1. Python Pandas 环境 #

如果你的数据是存在Pandas DataFrame中，可借助collections.Counter快速统计列表最高频元素：

import pandas as pd
from collections import Counter
import ast

# 第一步：如果STATE列是字符串格式的数组（比如从csv读出来默认是字符串），先转换为Python列表
df['STATE'] = df['STATE'].apply(ast.literal_eval)

# 第二步：定义统计最高频州的函数
def get_resident_state(state_list):
    # Counter.most_common(1)返回频次最高的(元素, 计数)元组
    return Counter(state_list).most_common(1)[0][0]

# 第三步：生成结果表
result_df = pd.DataFrame()
result_df['USER_ID'] = df['USER_ID']
result_df['STATE'] = df['STATE'].apply(get_resident_state)

运行后result_df就是你需要的输出格式。

2. SQL 环境（适配Hive/Spark SQL等支持数组函数的数仓） #

如果数据是存在数仓表中，可通过炸数组+窗口排序的方式实现：

-- 先统计每个用户每个州的出现次数
WITH state_freq AS (
    SELECT
        USER_ID,
        state_item AS STATE,
        COUNT(1) AS appear_cnt
    FROM ADDRESSES
    -- 炸开STATE数组，每个元素生成一行
    LATERAL VIEW EXPLODE(STATE) tmp AS state_item
    GROUP BY USER_ID, state_item
),
-- 给每个用户的州按出现次数倒序排名
state_rn AS (
    SELECT
        USER_ID,
        STATE,
        ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY appear_cnt DESC) AS rn
    FROM state_freq
)
-- 取排名第一的就是常驻州
SELECT USER_ID, STATE
FROM state_rn
WHERE rn = 1
ORDER BY USER_ID;

注：如果存在多个州出现频次并列最高的情况，把ROW_NUMBER替换为RANK即可返回所有并列最高的州，当前示例数据无并列情况，两种写法结果一致。

内容的提问来源于stack exchange，提问作者Marcos Dias