count distinct case when 条件 then 结果 end 的使用

最新推荐文章于 2025-03-17 02:05:28 发布

至学者

最新推荐文章于 2025-03-17 02:05:28 发布

阅读量6.2k

点赞数 2

分类专栏： MySQL 文章标签： SQL

本文链接：https://blog.csdn.net/calm_encode/article/details/121501223

版权

MySQL 专栏收录该内容

64 篇文章

订阅专栏

该博客介绍了如何利用SQL进行数据统计和分析，关注于用户行为，如总下单用户数、新增下单用户数、复购用户数及复购率。通过countdistinctcasewhen等关键字，结合时间范围查询，计算了指定时间段内的订单量、客单价，并且深入探讨了用户在前14天内的复购情况，提供了全面的用户行为洞察。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一概述

当我需要统计条件计算然后去重实现数据查询的时候，我们就可以通过：count distinct case when等关键字实现逻辑查询。

二使用过程

SET @start_time = '2022-10-18 00:00:00',
@end_time = '2022-10-19 23:59:59';

SELECT
  concat( LEFT ( @start_time, 10 ), ( '至' ), LEFT ( @end_time, 10 ) ) AS '时间',
  u.*,
  l.* 
FROM
  (
  SELECT
    count( DISTINCT pl.uid ) AS '总下单用户数',-- 当前用户是第一次下单购买商品
    count( DISTINCT pl.uid ) - count( DISTINCT pll.uid ) AS '新增下单用户数',
    count( pl.id ) AS '总订单量',-- 当周成功下单数≥2的用户数
    count( DISTINCT CASE WHEN pls.order_num > 1 THEN pl.uid END ) AS '当前周期复购用户数',-- 当前周期用户数/总下单用户数
    count( DISTINCT CASE WHEN pls.order_num > 1 THEN pl.uid END ) / count( DISTINCT pl.uid ) AS '当前周期复购率',-- 最近的订单时间和实际支付时间之间的差值为14天以内
    count( DISTINCT CASE WHEN TIMESTAMPDIFF( DAY, pll.pay_time,pl.pay_time ) <= 14 OR pls.order_num > 1 THEN  pl.uid END ) AS '当前周期前14天复购用户数',
    count( DISTINCT CASE WHEN TIMESTAMPDIFF( DAY, pll.pay_time,pl.pay_time ) <= 14 OR pls.order_num > 1 THEN  pl.uid END) / count( DISTINCT pl.uid ) AS '当前周期前14天复购率',
    round( sum( pl.real_money ) / 100 / count( pl.uid ), 2 ) AS '客单价' 
      FROM
        (-- 查找支付时间在一定范围内的订单
        SELECT
          id,
          uid,
          pay_time,
          real_money 
        FROM
          print_loan 
        WHERE
          is_refund = 0 
          AND STATUS IN ( 3, 4, 5, 6, 12, 8, 9 ) 
          AND pay_time BETWEEN @start_time 
          AND @end_time 
        ) pl
        left JOIN (-- 查找一定时间范围内下单用户，以及下单支付的最大时间
        SELECT
          uid,
          max(pay_time) pay_time
        FROM
          print_loan 
        WHERE
          is_refund = 0 
          AND STATUS IN ( 3, 4, 5, 6, 12, 8, 9 ) 
          AND pay_time < @start_time 
        GROUP BY
          uid 
        ) pll ON pl.uid = pll.uid
        LEFT JOIN (
        SELECT
          id,
          uid,
          count( id ) AS order_num 
        FROM
          print_loan 
        WHERE
          is_refund = 0 
          AND STATUS IN ( 3, 4, 5, 6, 12, 8, 9 ) 
          AND pay_time BETWEEN @start_time 
          AND @end_time 
        GROUP BY
          uid 
        ) pls ON pl.uid = pls.uid 
      ) l,
  ( SELECT count( id ) AS '总登录用户数' FROM print_user WHERE active_time BETWEEN @start_time AND @end_time ) u

结果