PostgreSQL中数组类型列session_ids的统计方法咨询

基础统计：所有元素数量的行分布 #

使用cardinality()函数直接获取一维数组的元素个数，分组统计各数量对应的行数及占比：

SELECT
  cardinality(session_ids) AS session_count,
  COUNT(*) AS row_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sensemyfeup.trips), 2) AS percentage
FROM sensemyfeup.trips
GROUP BY session_count
ORDER BY session_count;

说明 #

• cardinality()是PostgreSQL 9.3+支持的函数，专门用于返回数组的元素总数（一维数组直接返回元素个数），适配当前场景。
• 结果会列出每个元素数量对应的行数，以及该数量的行占总表行数的百分比。

仅统计1个、2个元素的行 #

如果只需要聚焦1个或2个session_id的行，添加过滤条件即可：

SELECT
  cardinality(session_ids) AS session_count,
  COUNT(*) AS row_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sensemyfeup.trips), 2) AS percentage
FROM sensemyfeup.trips
WHERE cardinality(session_ids) IN (1, 2)
GROUP BY session_count
ORDER BY session_count;

兼容老版本PostgreSQL（9.3以下） #

若使用的PostgreSQL版本低于9.3，没有cardinality()函数，可用array_length()替代（需指定数组维度，当前是一维数组，传入1）：

SELECT
  array_length(session_ids, 1) AS session_count,
  COUNT(*) AS row_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sensemyfeup.trips), 2) AS percentage
FROM sensemyfeup.trips
GROUP BY session_count
ORDER BY session_count;

处理空数组情况 #

如果session_ids可能存在空数组（{}），array_length()会返回NULL，可通过COALESCE()将其转为0，统计空数组的行：

SELECT
  COALESCE(array_length(session_ids, 1), 0) AS session_count,
  COUNT(*) AS row_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sensemyfeup.trips), 2) AS percentage
FROM sensemyfeup.trips
GROUP BY session_count
ORDER BY session_count;

按分类汇总（1个、2个、更多） #

如果需要把超过2个的行归为一类，用CASE语句分组：

SELECT
  CASE
    WHEN cardinality(session_ids) = 1 THEN '1个session'
    WHEN cardinality(session_ids) = 2 THEN '2个session'
    ELSE '3个及以上session'
  END AS session_category,
  COUNT(*) AS row_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM sensemyfeup.trips), 2) AS percentage
FROM sensemyfeup.trips
GROUP BY session_category
ORDER BY session_category;

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