不常见的 SQL

窗口函数

OVER

OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY 子句对数据进行分组，能够在同一行中同时返回基础行的原始列和聚合列。

在这里要引入窗口函数/开窗函数（Window Function）的概念：

CREATE TABLE Employee
(
ID INT  PRIMARY KEY,
Name VARCHAR(20),
GroupName VARCHAR(20),
Salary INT
)
INSERT INTO  Employee
VALUES(1,'小明','开发部',8000),
      (4,'小张','开发部',7600),
      (5,'小白','开发部',7000),
      (8,'小王','财务部',5000),
      (9, null,'财务部',NULL),
      (15,'小刘','财务部',6000),
      (16,'小高','行政部',4500),
      (18,'小王','行政部',4000),
      (23,'小李','行政部',4500),
      (29,'小吴','行政部',4700);
      
SELECT *,
     SUM(Salary) OVER(PARTITION BY Groupname) 每个组的总工资,
     SUM(Salary) OVER(PARTITION BY groupname ORDER BY ID) 每个组的累计总工资,
     SUM(Salary) OVER(ORDER BY ID) 累计工资,
     SUM(Salary) OVER() 总工资
from Employee

• SUM(Salary) OVER (PARTITION BY Groupname)
  • 只对PARTITION BY后面的列Groupname进行分组，分组后求解Salary的和。
• SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)
  • 对PARTITION BY后面的列Groupname进行分组，然后按ORDER BY 后的ID进行排序，然后在组内对Salary进行累加处理。
• SUM(Salary) OVER (ORDER BY ID)
  • 只对ORDER BY 后的ID内容进行排序，对排完序后的Salary进行累加处理。
• SUM(Salary) OVER ()
  • 对Salary进行汇总处理

这里的主标题就是开窗函数。

比较容易出错而且难以理解的是：COUNT(DISTINCT wuid) AS unique_wuid_count, SUM(COUNT(DISTINCT wuid)) OVER() AS total_unique_wuid_count from这样的设计。左列的累积和不一定等于右边的值，这样的bug比较难调试处理。可能的原因是跨分区可能存在重复值，SUM(COUNT(DISTINCT wuid)) OVER()会导致更多的去重，导致 total_unique_wuid_count 不等于的累加。

选出通话时间最早的一条记录：

select a,l,created_time,row_number() over(partition by a,l order by created_time asc) as rn
                from tmp
                where impl_date=%(i_datefrom)s 
                and to_char(created_time, 'yyyyMMdd') between 
                    %(i_date_pre_m1)s
                    and %(i_datefrom)s) tmp_cri
            where rn=1

case when

嵌套 case when

在select 的一个目标列里：

-- 在减法以前有一个 case when。在减法外有一个 case when，when 和 then 之间是一个减法
case when ((case when cri.call_time is null then 9999999999 else unix_timestamp(cri.call_time) end)-unix_timestamp(a.created_time)) <= 7 * 24 * 60 * 60 then 1 else 0 end as is_call_in_time

参考：《SQL窗口函数OVER详细用法，一学就会（上）》

缺列的时候mock一列

select “mock_id”, taskid from abc;

distinct 与 asc

select distinct a, b，会对 a 和 b 的组合去重。

order by a asc, b desc，会先对 a 升序排，再对 b 降序排。

distinct 只能写一次，而 asc 必须每列都写。

临时表

使用临时表来当数组

WITH used_names AS (
    SELECT 'C' AS cp_name
    UNION ALL SELECT 'D'
), all_names AS (    
    SELECT 'A' AS name
    UNION ALL SELECT name FROM used_names) -- 每多一行，加入一个UNION ALL，各行可以不加 AS name
INSERT OVERWRITE TABLE db.tbl_1
PARTITION (ds = '${last_day}')
SELECT a, b

FROM    db.tbl_2 a
INNER JOIN all_names n
ON      a.cp_name = n.cp_name
LEFT JOIN used_names u
ON      a.cp_name = u.cp_name

在SQL中定义多个CTE（公用表表达式）时，只有第一个CTE前面使用WITH关键字
后续的CTE应该用逗号分隔，而不是每个都使用WITH关键字。

CTE是"Common Table Expression"（公用表表达式）的缩写。它是SQL中的一种临时结果集，可以在SELECT、INSERT、UPDATE或DELETE语句中引用。

基于全量表生成差异天表

1. 全量表每个 ds 都有更新的全量数据。
2. 但是查询的时候用 gmtCreate 只查询天增量。

这样我们就得到了每天新增的新增-天增量表。

join on 可以代替 where in

可以把某些本地的条件移动到另一张表上去，把必须等于用 INNER JOIN b 来表达，把可以等于，但是不必须等于用 left join b 但是 b 的特定列 IS NOT NULL 来表达。

用两个时间段来 union all 来插入 insert overwrite 的时候

union all 的两侧不可时间重叠，不然会插入重复数据。

查询某个通话记录在历史上第一次出现的记录

(
-- 首先在当天的分区，生成当天的通话数据
-- 然后在全部的天分区（每个分区都有当天的全量记录，但是当天的记录也可能漂移到其他分区去，所以使用全部天分区）查询这些通话数据的电话的全部通话数据
-- 然后查找全部的通话数据里最早的通话时间

first_call_dates AS (
  SELECT 
    phone_no,
    MIN(call_date) AS first_call_date
  FROM all_history_calls
  GROUP BY phone_no
))

-- 最终聚合结果
SELECT 
  t.call_date AS stat_date,
  COUNT(t.phone_no) AS total_calls_today,
  COUNT(CASE WHEN f.first_call_date = t.call_date 
            -- 如果统计全行业，则去掉这个 AND
             AND GET_JSON_OBJECT(t.content, '$.info.bizType') IN ('life','dining','life_cpc_reach_store','life_cpc_popularized','home_industry_customer_service','life_cpc_latent') THEN 1 END) AS first_time_called_today
FROM today_calls t
LEFT JOIN first_call_dates f ON t.phone_no = f.phone_no
GROUP BY t.call_date;

对于同一个结果表而言，我们在 select 里再进行 case when，可以保证 where 里取出全量数据，然后在其他查询衍生子句里产生其他过滤列和过滤列的聚合结果。

WHERE、CASE WHEN和HAVING的核心区别

WHERE

• 作用：过滤原始数据行
• 执行时机：第一步执行，在数据检索时
• 核心特点：
  • 用于行级过滤，决定哪些行参与后续处理
  • 不能使用聚合函数
  • 性能最高，因为它减少了需要处理的数据量
  • 过滤发生在分组之前
• 示例：

WHERE GET_JSON_OBJECT(content, '$.log_type') = 'call_end'
  AND SUBSTR(GET_JSON_OBJECT(content, '$.data.start_time'), 1, 10) = '2025-12-07'

CASE WHEN表达式

• 作用：条件表达式，根据条件返回不同值
• 执行时机：在SELECT、WHERE、ORDER BY等子句中执行相应的计算
• 核心特点：
  • 不是过滤器，而是条件计算工具
  • 可以在SQL的多个位置使用
  • 不会减少行数，而是改变列值或计算逻辑
  • 可以嵌套在聚合函数中使用
• 示例：

SELECT 
  phone_no,
  call_date,
  call_status,
  -- 根据通话状态创建中文描述
  CASE 
    WHEN call_status = 'ANSWERED' THEN '已接听'
    WHEN call_status = 'NO_ANSWER' THEN '未接听'
    WHEN call_status = 'BUSY' THEN '占线'
    ELSE '其他状态'
  END AS status_description,
  -- 根据通话时长分类
  CASE 
    WHEN duration < 30 THEN '短通话'
    WHEN duration BETWEEN 30 AND 120 THEN '中等通话'
    WHEN duration > 120 THEN '长通话'
    ELSE '未知'
  END AS duration_category
FROM calls;

SELECT 
  call_date,
  COUNT(*) AS total_calls,
  -- 统计接听的通话数
  COUNT(CASE WHEN call_status = 'ANSWERED' THEN 1 END) AS answered_calls,
  -- 统计未接听的通话数
  COUNT(CASE WHEN call_status IN ('NO_ANSWER', 'BUSY') THEN 1 END) AS unanswered_calls,
  -- 计算接听率
  ROUND(
    COUNT(CASE WHEN call_status = 'ANSWERED' THEN 1 END) * 100.0 / COUNT(*), 2
  ) AS answer_rate,
  -- 计算平均有效通话时长（只计算接听的）
  AVG(CASE WHEN call_status = 'ANSWERED' THEN duration END) AS avg_effective_duration
FROM calls
GROUP BY call_date;

SELECT *
FROM calls
WHERE 
  -- 根据不同业务类型设置不同的通话时长要求
  CASE 
    WHEN biz_type = 'life' THEN duration >= 30
    WHEN biz_type = 'dining' THEN duration >= 60
    WHEN biz_type = 'home_industry' THEN duration >= 45
    ELSE duration >= 15
  END = TRUE
  -- 并且满足其他条件
  AND call_status = 'ANSWERED';
  
SELECT *
FROM calls
WHERE 
  -- 工作日和周末使用不同的时间范围
  CASE 
    WHEN DAYOFWEEK(call_date) IN (1, 7) THEN  -- 周末
      start_time BETWEEN '09:00:00' AND '20:00:00'
    ELSE  -- 工作日
      start_time BETWEEN '08:00:00' AND '18:00:00'
  END = TRUE;
  
SELECT 
  -- 根据通话时长动态分组
  CASE 
    WHEN duration < 30 THEN '0-30秒'
    WHEN duration BETWEEN 30 AND 60 THEN '30-60秒'
    WHEN duration BETWEEN 61 AND 120 THEN '1-2分钟'
    WHEN duration > 120 THEN '2分钟以上'
    ELSE '未知'
  END AS duration_group,
  COUNT(*) AS call_count,
  AVG(duration) AS avg_duration
FROM calls
GROUP BY 
  CASE 
    WHEN duration < 30 THEN '0-30秒'
    WHEN duration BETWEEN 30 AND 60 THEN '30-60秒'
    WHEN duration BETWEEN 61 AND 120 THEN '1-2分钟'
    WHEN duration > 120 THEN '2分钟以上'
    ELSE '未知'
  END
ORDER BY avg_duration;

SELECT phone_no, biz_type, call_status, duration
FROM calls
ORDER BY 
  -- 按业务类型重要性排序，1是升序排序的第一位
  CASE biz_type
    WHEN 'life' THEN 1
    WHEN 'dining' THEN 2
    WHEN 'home_industry' THEN 3
    ELSE 4
  END,
  -- 然后按通话状态排序
  CASE call_status
    WHEN 'ANSWERED' THEN 1
    WHEN 'NO_ANSWER' THEN 2
    WHEN 'BUSY' THEN 3
    ELSE 4
  END,
  -- 最后按时长降序排列
  duration DESC;
  
SELECT phone_no
FROM calls
GROUP BY phone_no
HAVING 
  -- 只保留接听率超过80%的号码
  COUNT(CASE WHEN call_status = 'ANSWERED' THEN 1 END) * 100.0 / COUNT(*) > 80
  -- 并且总通话次数大于10次
  AND COUNT(*) > 10;

case when 本质上还是一种 expression，WHEN biz_type = 'life' THEN duration >= 30返回的还是布尔表达式。

HAVING 子句

• 作用：过滤分组结果
• 执行时机：第四步执行，在GROUP BY之后
• 核心特点：
  • 专门用于过滤 GROUP BY 产生的分组
  • 只能使用聚合函数
  • 过滤的是分组，不是原始行-只有满足条件的分组才会保留下来
  • 必须与 GROUP BY 一起使用

SELECT tc.phone_no
FROM thirty_days_calls tc
GROUP BY tc.phone_no
-- 只有当这个 phone_no 的所有通话记录的 call_status 都不在 '200' 和 'ANSWERED' 之中时，才会被保留下来。
HAVING COUNT(*) = SUM(CASE WHEN tc.call_status NOT IN ('200', 'ANSWERED') THEN 1 ELSE 0 END);

我们实际上是想过滤号码行，但是我们把号码行转化为分组：
- 这样我们可以按次数，或者case when 转化条件，按分布特征来过滤符合特定分布特征的代码行（分组）-虽然我们已经先把多行转化为一个分组了。
- 我们尽量不使用聚合函数，毕竟我们只是想要取出行的核心值，不需要衍生值。
- 在这个例子里，我们的 having 的聚合函数内部的表达式，仍然是把这个分组里的每一行做转化。

特殊的常量在 count 和 select 里的用法

-- 这些都是有效的
COUNT(1)        -- 统计行数
COUNT(*)        -- 统计行数
SUM(1)          -- 每行加1，相当于统计行数
MAX(1)          -- 常量的最大值永远是1
MIN(1)          -- 常量的最小值永远是1
AVG(1)          -- 常量的平均值永远是1

-- COUNT的特殊情况
COUNT(*)        -- 统计所有行（包括NULL）
COUNT(1)        -- 统计所有行（1永不为NULL）
COUNT(column)   -- 只统计非NULL值
COUNT(NULL)     -- 结果总是0

-- 其他聚合函数
SUM(1)          -- 等于行数
SUM(5)          -- 等于行数×5
AVG(10)         -- 等于10（常量的平均值就是常量本身）

-- 这些都是有效的SELECT语句
SELECT 1 FROM table_name;                    -- 每行都返回1
SELECT 'constant_value' FROM table_name;     -- 每行都返回相同字符串
SELECT 1 AS row_indicator FROM table_name;   -- 每行都返回1，并起别名
SELECT phone_no, 1 AS flag FROM calls;       -- 返回实际列+常量列

-- 假设有一张表，其中some_column有NULL值：
-- | id | some_column |
-- |----|-------------|
-- | 1  | A           |
-- | 2  | NULL        |
-- | 3  | B           |

SELECT 
  COUNT(*) AS total_rows,           -- 结果: 3
  COUNT(1) AS also_total_rows,      -- 结果: 3
  COUNT(some_column) AS non_null_values  -- 结果: 2
FROM table_name;

-- 统计行数的不同方式
SELECT 
  COUNT(*) AS row_count_method1,
  SUM(1) AS row_count_method2,      -- 每行加1，等效于COUNT(*)
  SUM(2) AS double_row_count        -- 每行加2，等于行数×2
FROM calls;