SQLite查询与dplyr代码结果不一致问题排查

需求背景 #

使用posts.csv（2017年美国国会议员的10000条公开Facebook帖子样本），需完成以下数据处理：

• 排除点赞数（likes_count）为0的帖子
• 计算每条帖子的评论点赞比clr = comments_count / likes_count
• 针对每个screen_name，仅用**偶数月份（2、4、6、8、10、12月）**的帖子计算归一化因子normaliser_based_on_even_months = max(clr) - min(clr)
• 删除归一化因子为0的用户记录
• 计算normalised_clr = clr / normaliser_based_on_even_months，删除该值≤0的记录
• 按normalised_clr升序排列，输出前10条的screen_name和normalised_clr

问题描述 #

已用R的dplyr实现上述逻辑，但转换为SQLite查询后，输出结果与dplyr完全不一致，无法定位差异原因。

原SQLite代码的核心错误 #

错误1：cte2未保留用户标识 #

原cte2的SELECT语句未包含screen_name，仅计算了归一化因子，导致后续无法将因子关联到正确的用户。

错误2：交叉连接导致笛卡尔积 #

原cte3使用FROM cte1, cte2的交叉连接，未按screen_name关联，导致每个帖子匹配了所有用户的归一化因子，计算出的normalized_clr完全错误。

错误3：月份类型不严谨 #

原代码用字符串类型的月份进行取模运算，SQLite自动转换可能引发潜在问题。

修正后的SQLite查询代码 #

WITH 
cte1 AS (
    SELECT 
        screen_name, 
        comments_count * 1.0 / likes_count AS clr, 
        CAST(strftime('%m', date) AS INTEGER) AS month 
    FROM posts 
    WHERE likes_count > 0
), 
cte2 AS (
    SELECT 
        screen_name,
        (MAX(clr) - MIN(clr)) AS normaliser_based_on_even_months 
    FROM cte1 
    WHERE month % 2 = 0
    GROUP BY screen_name
    HAVING normaliser_based_on_even_months > 0 -- 提前过滤无效因子
),
cte3 AS (
    SELECT 
        c1.screen_name, 
        c1.clr, 
        c2.normaliser_based_on_even_months,
        c1.clr / c2.normaliser_based_on_even_months AS normalized_clr 
    FROM cte1 c1
    INNER JOIN cte2 c2 ON c1.screen_name = c2.screen_name -- 精准关联用户
    WHERE c1.clr > 0
)
SELECT screen_name, normalized_clr 
FROM cte3 
WHERE normalized_clr > 0 
ORDER BY normalized_clr ASC
LIMIT 10;

修正说明 #

1. cte2添加screen_name：确保归一化因子与用户一一对应，为后续关联提供依据
2. 改用INNER JOIN关联：按screen_name将cte1（所有有效帖子）与cte2（有效用户的归一化因子）关联，保证每个帖子使用对应用户的因子
3. 提前过滤无效因子：在cte2中用HAVING过滤归一化因子为0的用户，减少后续计算量
4. 月份转换为整数：避免字符串取模的潜在问题，逻辑与dplyr的month(date)保持一致
5. 添加LIMIT 10：直接获取前10条结果，提升查询效率

参考dplyr代码与输出（原内容） #

dplyr代码 #

posts <- read.csv("C:/Users/HP/Documents/posts.csv")

# 排除点赞数为0的帖子
posts <- posts %>% filter(likes_count > 0)

# 计算评论点赞比clr
posts <- posts %>% mutate(clr = comments_count / likes_count) 

# 处理日期，提取月份
posts$date <- ymd(posts$date)
posts$date <- month(posts$date)

# 计算每个用户基于偶数月的归一化因子
posts_normaliser <- posts %>% 
  group_by(screen_name) %>% 
  mutate(normaliser_based_on_even_months = case_when(date%%2==0 ~ (max(clr) - min(clr))))

# 过滤归一化因子为0的记录
posts_normaliser <- posts_normaliser %>% filter(normaliser_based_on_even_months > 0)

# 关联原帖子与归一化因子，计算normalised_clr
merged_df <- merge(posts, posts_normaliser)
merged_df <- merged_df %>% 
  group_by(screen_name) %>% 
  mutate(normalised_clr = clr / normaliser_based_on_even_months)

# 过滤有效记录并排序
merged_df <- merged_df %>% 
  filter(normalised_clr > 0) %>% 
  arrange(normalised_clr)

# 输出前10条
merged_df[1:10, c("screen_name", "normalised_clr")]

dplyr输出 #

# A tibble: 10 × 2
# Groups:   screen_name [5]
   screen_name                   normalised_clr
   <chr>                                  <dbl>
 1 CongresswomanSheilaJacksonLee        0.00214
 2 CongresswomanSheilaJacksonLee        0.00218
 3 CongresswomanSheilaJacksonLee        0.00277
 4 RepMullin                            0.00342
 5 SenDuckworth                         0.00342
 6 CongresswomanSheilaJacksonLee        0.00357
 7 replahood                            0.00477
 8 SenDuckworth                         0.00488
 9 SenDuckworth                         0.00505
10 RepSmucker                           0.00516

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