Impala按clid、pid分组统计lid数量时如何获取lid样本值？

报错原因 #

你编写的语句报错是因为窗口函数first_value的执行顺序早于GROUP BY分组聚合，此时lid既没有出现在GROUP BY子句中，也没有被聚合函数包裹，不符合Impala的语法规则。

解决方案 #

针对海量数据场景，推荐两种实现方式：

方案1：使用ANY_VALUE聚合函数（性能最优）

Impala 2.11及以上版本支持ANY_VALUE函数，会直接返回分组内任意一个非NULL的lid值，无需额外排序，性能最高，适合对样本取值没有固定规则的场景：

SELECT 
  clid, 
  pid, 
  COUNT(lid) AS `count`,
  ANY_VALUE(lid) AS `sample lid`
FROM t
GROUP BY clid, pid;

方案2：窗口函数预计算（支持自定义样本规则）

如果你需要按照指定规则取样本（比如排序后的第一个值、随机值），可以先用CTE给分组内的行打标记，再过滤取单条：

WITH grouped_data AS (
  SELECT 
    clid,
    pid,
    lid,
    COUNT(lid) OVER(PARTITION BY clid, pid) AS `count`,
    -- 下面的ORDER BY可自定义规则：要随机样本就写ORDER BY RAND()，要按lid排序就写ORDER BY lid
    ROW_NUMBER() OVER(PARTITION BY clid, pid) AS rn
  FROM t
)
SELECT 
  clid,
  pid,
  `count`,
  lid AS `sample lid`
FROM grouped_data
WHERE rn = 1;

补充说明 #

你原统计语句里的frim是拼写错误，需要修正为FROM。

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