为了更快的定位您的问题，请提供以下信息，谢谢
【详述】3.3.5版本升级到3.3.13版本后，反馈查询结果有问题，当使用case when时，匹配不上的数据现在为0或者为null，理论上应该是null
【背景】
【业务影响】很多bi看板出现问题
【是否存算分离】否
【StarRocks版本】例如：3.3.13
【集群规模】例如：3fe（1 follower+2observer）+5be（fe与be混部）
【机器信息】CPU虚拟核/内存/网卡，例如：48C/64G/万兆
【联系方式】
【附件】
查询SQL
WITH
t1 AS
(
SELECT *
,rank() OVER (PARTITION BY invite_user_id ORDER BY register_date ) AS rank_day
,rank() OVER (PARTITION BY invite_user_id ,substring(register_date,1,7 ) ORDER BY register_date ) AS rank_month
,substring(register_date,1,7) AS register_month
,substring(invite_deposit_date,1,7) AS invite_deposit_month
FROM
(
SELECT invite_user_id
,invite_register_date
,invite_deposit_date
,t1.user_id
,register_date
,hour
FROM --用户注册信息
(
SELECT b.user_id AS invite_user_id
,a.user_id
,cast(substring(CONVERT_TZ(a.register_time,‘UTC’,‘Asia/Bangkok’),12,2) AS int) AS hour
,substring(CONVERT_TZ(a.register_time,‘UTC’,‘Asia/Bangkok’),1,10) AS register_date
FROM dim_user_register_info_view a
JOIN dim_user_invite_channel b
ON a.user_id = b.invited_user_id
WHERE channel_1 = ‘用户引荐’
)t1
LEFT JOIN
(
SELECT user_id
,substring(CONVERT_TZ(register_time,‘UTC’,‘Asia/Bangkok’),1,10) AS invite_register_date
FROM dim_user_register_info_view
)t2
ON t1.invite_user_id = t2.user_id
LEFT JOIN
(
SELECT user_id
,a.account_id
,substring(CONVERT_TZ(complete_time,‘UTC’,‘Asia/Bangkok’),1,10) invite_deposit_date
FROM
(
SELECT *
,ROW_NUMBER() over(PARTITION BY account_id ORDER BY complete_time) AS rankk
FROM dim_t_deposit
WHERE status = ‘COMPLETED’
)a
LEFT JOIN
(
SELECT user_id
,account_id
,account_number
FROM dim_t_application_request
GROUP BY user_id
,account_id
,account_number
)b
ON a.account_id = b.account_id
WHERE rankk = 1
)t3
ON t1.invite_user_id = t3.user_id
)s1
)
SELECT
–*,
invite_user_id,
–rank_day=1,invite_deposit_month=register_month,
CASE WHEN rank_day = 1 AND invite_deposit_month = register_month THEN invite_user_id end
–COUNT(distinct CASE WHEN rank_day = 1 AND invite_deposit_month = register_month THEN invite_user_id else null end) AS new_user_first_inviters
FROM t1
WHERE register_date > ‘2025-06-01’
– AND invite_deposit_month = register_month
– AND rank_day = 1
LIMIT 1000
explain:
explain.txt (16.6 KB)
实际结果：
预期结果应该是为null