数据离散化SQL

场景CASE WHEN除法，取整，均匀划分区间

年龄分段统计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;

按规则划分年龄段

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;