如何按user_email与去除分区后缀的table_id分组统计数据？

我有一张存储其他分区表详情的表，原始数据如下：

Row    user_email      table_id           job_type
1     jack@test.com   product$20230310   Query
2     john@test.com   item$20230309      Query
3     jim@test.com    product$20230211   Query
4     jack@test.com   product$20220105   Query
5     Alex@test.com   item$20230310      Query

我希望按user_email和去除分区后缀的基础表名分组统计，但当前按user_email和table_id分组的查询会为每个分区生成单独一行。

当前使用的查询语句：

SELECT
  user_email,
  table_id,
  COUNT(*) AS total
FROM
  TABLE
GROUP BY
  user_email,
  table_id

当前查询结果：

Row    user_email      table_id           total
1     jack@test.com   product$20230310   1
2     john@test.com   item$20230309      1
3     jim@test.com    product$20230211   1
4     jack@test.com   product$20220105   1
5     Alex@test.com   item$20230310      1

期望输出结果：

Row    user_email      table_id           total
1     jack@test.com   product            2
2     john@test.com   item               1
3     jim@test.com    product            1
5     Alex@test.com   item               1

请问如何修改查询语句，实现按user_email和去除分区后缀的基础表名分组统计？

核心思路是从table_id中提取$符号之前的部分作为基础表名，再按该字段与user_email分组统计。以下是不同SQL方言的实现方式：

1. BigQuery/GCP SQL #

使用SPLIT函数分割字符串后取第一个元素：

SELECT
  user_email,
  SPLIT(table_id, '$')[OFFSET(0)] AS table_id,
  COUNT(*) AS total
FROM
  TABLE
GROUP BY
  user_email,
  SPLIT(table_id, '$')[OFFSET(0)]

2. MySQL/MariaDB #

使用SUBSTRING_INDEX函数直接截取$之前的内容：

SELECT
  user_email,
  SUBSTRING_INDEX(table_id, '$', 1) AS table_id,
  COUNT(*) AS total
FROM
  TABLE
GROUP BY
  user_email,
  SUBSTRING_INDEX(table_id, '$', 1)

3. PostgreSQL #

使用SPLIT_PART函数分割后获取第一部分：

SELECT
  user_email,
  SPLIT_PART(table_id, '$', 1) AS table_id,
  COUNT(*) AS total
FROM
  TABLE
GROUP BY
  user_email,
  SPLIT_PART(table_id, '$', 1)

4. SQL Server #

先通过CHARINDEX定位$的位置，再用LEFT截取对应内容：

SELECT
  user_email,
  LEFT(table_id, CHARINDEX('$', table_id) - 1) AS table_id,
  COUNT(*) AS total
FROM
  TABLE
WHERE CHARINDEX('$', table_id) > 0 -- 可选：过滤无分区后缀的表
GROUP BY
  user_email,
  LEFT(table_id, CHARINDEX('$', table_id) - 1)

如果存在无$后缀的table_id，可以用CASE做兼容处理：

LEFT(table_id, CASE WHEN CHARINDEX('$', table_id) > 0 THEN CHARINDEX('$', table_id) - 1 ELSE LEN(table_id) END) AS table_id

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