玩家分类SQL查询异常：无法正确区分Lottery类玩家

问题背景 #

需按玩家活动进行分类：

• 仅参与Lottery的玩家标记为lottery_player
• 同时参与Lottery及其他品类（如Bingo、Slots等），且Lottery投注占比超过50%的玩家标记为lotto_mainly
  现有SQL无法正确区分两类玩家，分类结果异常。

原SQL代码 #

with player_activity as ( 
select
PLAYER_ID,  
sum(case when GENRE_ID  ='Lottery' then 1 else 0 end) as lotto_bets,
sum(case when GENRE_ID  ='Sportsbook' then 1 else 0 end) as Sportsbook_bets,  
sum(case when GENRE_ID  ='Scratchcard' then 1 else 0 end) as Scratchcard_bets,
sum(case when GENRE_ID  in ('Instant games', 'Games') then 1 else 0 end) as Slot_bets,
sum(case when GENRE_ID  ='Bingo' then 1 else 0 end) as Bingo_bets,
sum(case when GENRE_ID  ='Bundles' then 1 else 0 end) as Bundle_bets,
lotto_bets +Sportsbook_bets + Scratchcard_bets + Slot_bets + Bingo_bets + Bundle_bets  as total_bets,
lotto_bets/nullif(total_bets,0) *100 as lotto_percentage,
Sportsbook_bets/nullif(total_bets,0) *100 as sportsbook_percentage,
Scratchcard_bets/ nullif(total_bets,0) * 100 as scratchcard_percentage,
Slot_bets/ nullif(total_bets,0) * 100 AS slots_percentage,
Bingo_bets/ nullif(total_bets,0) * 100 AS Bingo_percentage,
Bundle_bets/ nullif(total_bets,0) * 100 AS Bundle_percentage, 
case
     when  lotto_percentage >50  then 'lotto_mainly'
     when  lotto_bets > '0' AND lotto_percentage = '100.000000' then 'lotto_only'
     end as favoured_product

FROM lottoprod_dw.TRANSACTION_LINES
GROUP BY 1)

问题分析 #

1. CASE条件顺序错误：先判断lotto_percentage >50会把投注占比100%的玩家也归类到lotto_mainly，永远触发不到后续的lotto_only条件，必须调换顺序。
2. 数据类型不匹配：lotto_bets是数值类型，却和字符串'0'比较；lotto_percentage是浮点数值，和字符串'100.000000'比较，可能导致匹配失效。
3. 字段引用顺序问题：部分SQL引擎不允许在SELECT中直接引用后续计算的字段（比如total_bets是后定义的，前面的lotto_percentage调用它可能报错或计算异常）。

修正后的SQL #

WITH player_activity AS (
    SELECT
        PLAYER_ID,
        SUM(CASE WHEN GENRE_ID = 'Lottery' THEN 1 ELSE 0 END) AS lotto_bets,
        SUM(CASE WHEN GENRE_ID = 'Sportsbook' THEN 1 ELSE 0 END) AS sportsbook_bets,
        SUM(CASE WHEN GENRE_ID = 'Scratchcard' THEN 1 ELSE 0 END) AS scratchcard_bets,
        SUM(CASE WHEN GENRE_ID IN ('Instant games', 'Games') THEN 1 ELSE 0 END) AS slot_bets,
        SUM(CASE WHEN GENRE_ID = 'Bingo' THEN 1 ELSE 0 END) AS bingo_bets,
        SUM(CASE WHEN GENRE_ID = 'Bundles' THEN 1 ELSE 0 END) AS bundle_bets,
        -- 直接用SUM(1)计算总投注数，避免字段引用顺序问题
        SUM(1) AS total_bets,
        -- 直接基于聚合结果计算占比，更可靠
        SUM(CASE WHEN GENRE_ID = 'Lottery' THEN 1 ELSE 0 END) / NULLIF(SUM(1), 0) * 100 AS lotto_percentage,
        CASE
            -- 优先判断仅玩Lottery的情况：总投注数等于Lottery投注数
            WHEN lotto_bets = total_bets THEN 'lottery_player'
            -- 再判断占比超过50%的混合玩家
            WHEN lotto_percentage > 50 THEN 'lotto_mainly'
        END AS favoured_product
    FROM lottoprod_dw.TRANSACTION_LINES
    GROUP BY PLAYER_ID
)
SELECT * FROM player_activity;

关键修正点 #

• 调换CASE条件顺序，优先匹配仅玩Lottery的玩家
• 所有比较操作使用数值类型（如lotto_bets > 0而非lotto_bets > '0'）
• 用SUM(1)直接计算总投注数，规避字段引用顺序问题
• 用lotto_bets = total_bets替代字符串匹配100%占比，逻辑更可靠

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