聚合函数

-- 统计海量日志中独立访问用户（UV）的近似数量
WITH user_logs AS (
    SELECT 1 AS user_id UNION ALL SELECT 1 UNION ALL 
    SELECT 2 AS user_id UNION ALL SELECT 3 UNION ALL 
    SELECT NULL AS user_id
)
SELECT 
    -- 1. 默认误差估算 (约 2.3%)
    approx_distinct(user_id) AS uv_default,
    -- 2. 指定更小的标准误差 (e=0.01 表示 1% 误差)
    approx_distinct(user_id, 0.01) AS uv_precise,
    -- 3. 使用 approx_count_distinct
    approx_count_distinct(user_id) AS uv_count_style
FROM user_logs;

-- 结果:
-- uv_default,uv_precise,uv_count_style
-- 3,3,3


-- 分析用户响应时间，获取中位数、P90（90% 分位数）和 P99 指标，以评估系统性能。
WITH response_times AS (
    SELECT 10 AS latency UNION ALL SELECT 20 AS latency UNION ALL 
    SELECT 30 AS latency UNION ALL SELECT 100 AS latency UNION ALL 
    SELECT 500 AS latency
)
SELECT 
    -- median: 计算 50% 位置的值
    median(latency) AS p50_median,
    -- approx_percentile: 计算 50% 分位数 (等同于中位数)
    approx_percentile(latency, 0.5) AS p50_approx,
    -- approx_percentile: 计算 90% 位置的值 (P90)
    approx_percentile(latency, 0.9) AS p90_latency,
    -- approx_percentile: 计算 99% 位置的值 (P99)
    approx_percentile(latency, 0.99) AS p99_latency
FROM response_times;

-- 结果:
-- p50_median,p50_approx,p90_latency,p99_latency
-- 30,30,500,500

-- 假设有一张订单明细表 order_items，我们需要判断每个订单是否已经完成支付（所有商品都已发货）以及是否存在退款项。
-- 创建临时数据
WITH order_items AS (
    SELECT 101 AS order_id, TRUE AS is_shipped, FALSE AS is_refunded UNION ALL
    SELECT 101 AS order_id, TRUE AS is_shipped, TRUE AS is_refunded  UNION ALL
    SELECT 102 AS order_id, TRUE AS is_shipped, FALSE AS is_refunded UNION ALL
    SELECT 102 AS order_id, FALSE AS is_shipped, FALSE AS is_refunded
)
-- 执行聚合查询
SELECT 
    order_id,
    -- 只有当订单下所有商品都已发货时，结果才为 TRUE
    bool_and(is_shipped) AS all_shipped,
    -- 只要订单中有一个商品发生退款，结果即为 TRUE
    bool_or(is_refunded) AS has_refund
FROM order_items
GROUP BY order_id;
-- 结果:
-- order_id,all_shipped,has_refund
-- 101,1,1
-- 102,0,0

-- 创建权限数据
WITH user_roles AS (
    -- 角色 A: Read (1) + Write (2) = 3
    SELECT 'User_A' AS user_name, 3 AS permission_mask UNION ALL
    -- 角色 B: Read (1) + Execute (4) = 5
    SELECT 'User_A' AS user_name, 5 AS permission_mask
)
-- 执行位运算聚合
SELECT 
    user_name,
    -- 位或：获取用户拥有的所有权限 (1 | 2 | 4 = 7)
    bitwise_or_agg(permission_mask) AS total_permissions,
    -- 位与：获取用户在所有角色中共同拥有的权限 (3 & 5 = 1)
    bitwise_and_agg(permission_mask) AS common_permissions,
    -- 位异或：获取角色间差异的权限位 (3 ^ 5 = 6)
    bitwise_xor_agg(permission_mask) AS diff_permissions
FROM user_roles
GROUP BY user_name;
-- 结果:
-- user_name,total_permissions,common_permissions,diff_permissions
-- User_A,7,1,6

-- 假设我们有一台设备，在不同时间点上报了温度（temperature）和固件版本（version）。其中版本号并不是每次都上报（存在 NULL）。
WITH sensor_readings AS (
    -- 模拟数据：ID, 时间, 温度, 固件版本
    SELECT 'Sensor_01' AS device_id, '2026-01-01 08:00' AS ts, 20.5 AS temp, 'v1.0' AS ver UNION ALL
    SELECT 'Sensor_01' AS device_id, '2026-01-01 08:15' AS ts, 22.1 AS temp, NULL AS ver   UNION ALL
    SELECT 'Sensor_01' AS device_id, '2026-01-01 08:30' AS ts, 19.8 AS temp, 'v1.1' AS ver UNION ALL
    SELECT 'Sensor_01' AS device_id, '2026-01-01 08:45' AS ts, 21.0 AS temp, NULL AS ver
)
SELECT 
    device_id,
    -- 1. 极值聚合
    min(temp) AS min_temp,             -- 最低温度
    max(temp) AS max_temp,             -- 最高温度
    
    -- 2. 位置聚合（包含NULL）
    `first`(ver) AS first_ver_raw,       -- 遇到的第一个值（v1.0）
    `last`(ver) AS last_ver_raw,         -- 遇到的最后一个值（NULL）
    
    -- 3. 忽略空值的位置聚合
    first_ignore_null(ver) AS first_valid_ver, -- 第一个非空版本（v1.0）
    last_ignore_null(ver) AS latest_ver        -- 最新有效版本（v1.1）
FROM sensor_readings
GROUP BY device_id;
-- 结果:
-- device_id,min_temp,max_temp,first_ver_raw,last_ver_raw,first_valid_ver,latest_ver
-- Sensor_01,19.8,22.1,v1.0,NULL,v1.0,v1.1

-- 构建一个模拟的生产线零件重量数据集，通过这些统计函数来评估生产工艺的稳定性。
WITH production_data AS (
    -- 模拟 5 个零件的重量（单位：克）
    SELECT 'Line_A' AS line_id, 10.2 AS weight UNION ALL
    SELECT 'Line_A' AS line_id, 9.8 AS weight  UNION ALL
    SELECT 'Line_A' AS line_id, 10.0 AS weight UNION ALL
    SELECT 'Line_A' AS line_id, 10.5 AS weight UNION ALL
    SELECT 'Line_A' AS line_id, 9.5 AS weight
)
SELECT 
    line_id,
    -- 1. 标准差：衡量数据偏离平均值的程度
    stddev_samp(weight) AS sample_std,    -- 样本标准差 (常用)
    stddev_pop(weight) AS pop_std,        -- 总体标准差
    
    -- 2. 方差：标准差的平方
    var_samp(weight) AS sample_variance,  -- 样本方差
    var_pop(weight) AS pop_variance,      -- 总体方差
    
    -- 3. 分布形态
    skewness(weight) AS data_skew,        -- 偏度：反映分布的对称性
    kurtosis(weight) AS data_kurt         -- 峰度：反映分布的陡峭程度/尾部厚度
FROM production_data
GROUP BY line_id;
-- 结果:
-- line_id,sample_std,pop_std,sample_variance,pop_variance,data_skew,data_kurt
-- Line_A,0.38078865529319522,0.34058768251719912,0.14499999999999985,0.11599996948243643,-5.2689553466917849e-17,-1.0945303210463697