数据离散化SQL

场景

年龄分段统计HIVE实现

-- 造数据
CREATE VIEW customer(name,age) AS
SELECT 'a',18 UNION ALL
SELECT 'b',14 UNION ALL
SELECT 'c',22 UNION ALL
SELECT 'd',23 UNION ALL
SELECT 'e',30 UNION ALL
SELECT 'f',18;
SELECT * FROM customer;

CASE WHEN

按规则划分年龄段

WITH
discretization_age AS(
    SELECT CASE
        WHEN age>=0 AND age<18 THEN '[0,18)'
        WHEN age>=18 AND age<23 THEN '[18,23)'
        WHEN age>=23 AND age<30 THEN '[23,30)'
        WHEN age>=30 AND age<40 THEN '[30,40)'
        WHEN age>=40 THEN '[40,+∞)'
    END section
    FROM customer
)
SELECT section,COUNT(1)cnt FROM
discretization_age
GROUP BY section;

除法，取整，均匀划分区间

每5年划分一个年龄段

WITH
discretization_age AS(
    SELECT (age div 5)a
    FROM customer
)
SELECT CONCAT('[',a*5,',',a*5+5,')')section,COUNT(1)cnt FROM
discretization_age
GROUP BY a;