sql中灵活运用join使代码更直观简洁，以及使用join... on 1=1计算分组百分比

最新推荐文章于 2023-12-07 18:19:00 发布

dair6

最新推荐文章于 2023-12-07 18:19:00 发布

阅读量5.2k

点赞数

分类专栏： sql相关问题文章标签： sql 数据库 database

本文链接：https://blog.csdn.net/dair6/article/details/121331585

版权

sql相关问题专栏收录该内容

14 篇文章 3 订阅

订阅专栏

本文探讨了两种方法：一是不使用JOIN，通过分组计数求得order_phone_num对应time_period的占比；二是利用JOIN简化查询，计算每个时间段内订单数量占比。展示了如何利用SQL技巧实现消费时间百分比的计算并比较其优劣。

摘要由CSDN通过智能技术生成

1.表a的结构如下：

在这里插入图片描述
需求如下：
求出time_period所占的比例

最终结果如下：

在这里插入图片描述

（1）不使用join：

     ceshi0 as (
         select order_phone_num,
                --需要注意的是无论count(*),count(time_period)还是其他字段，结果是一样的，因为group by根据的是order_phone_num,
                --除了order_phone_num这个字段，分组之后，其他字段下的数据都会合并在一起，无论这些数据是否有重复
                count(*)                                  as period_num,
                concat_ws('、', collect_list(time_period)) as time_period_all_past --如果出现重复不会去重，因为有可能两次购买都在同一个时间段

         from add_payment_period
         group by order_phone_num
     ),

     ceshi as (
         select order_phone_num,
                time_period,
                count(*) as num
         from add_payment_period
         group by order_phone_num,
                  time_period
     ),
     ceshi1 as (
         select    app.order_phone_num,
                ce.num,
                cusu.period_num

         from add_payment_period app
                  join ceshi ce
                       on app.order_phone_num = ce.order_phone_num
                           and app.time_period = ce.time_period
                  join ceshi0 cusu
                       on app.order_phone_num = cusu.order_phone_num
         group by  app.order_phone_num,
                  ce.num,
                  cusu.period_num
     ),
          ceshi2 as (
         select order_phone_num,
                time_period,
                CONCAT(cast((num) * 100 / (period_num) as DECIMAL(18, 2)), '%') as hundrend_per
         from ceshi1
         group by order_phone_num,
                  time_period,
                  hundrend_per
     ),
     ceshi3 as (
         select order_phone_num,
--                 cast(time_period as varchar(100)) + ' ' + cast(hundrend_per as varchar(100))
                concat_ws(' ', time_period, hundrend_per) as time_hundrend --用concat_ws将表格两列连接起来
         from ceshi2
     ),
     cusumeption_time_percent as (
         select order_phone_num,
                concat_ws('、', collect_list(time_hundrend)) as time_period_all --如果出现重复不会去重，因为有可能两次购买都在同一个时间段
         from ceshi3
         group by order_phone_num
     ),

（2）使用join

     cusumeption_time_percent2 as (
         select order_phone_num,
                concat_ws('、', collect_list(time_period_all_1)) as time_period_all
         from (
                  select a.order_phone_num,
                         time_period,
                         concat_ws(' ', time_period,
                                   CONCAT(cast((a1) * 100 / (b1) as DECIMAL(18, 2)), '%')) as time_period_all_1
                  from (
                        (select order_phone_num,
                                time_period,
                                count(*) as a1
                         from add_payment_period
                         group by order_phone_num, time_period
                        ) a

                           join (
                      select count(*) as b1,
                             order_phone_num
                      from add_payment_period
                      group by order_phone_num
                  ) b
                                on a.order_phone_num = b.order_phone_num

                      )
              )
         group by order_phone_num
     )

（2）和（1）实现相同的功能，但（2）语句更简洁

需要注意的是，join…on 1=1,类似于cross join，都可以求两张表的笛卡尔积，即使两张表没有相同的字段，也能将两张表关联起来

2.join … on 1 = 1

# 求分组后，每一组个数占分组前总条数百分比
select order_phone_num,
        concat(round(num/all_num,3),'%')
from (
    select order_phone_num
    from (
        select order_phone_num,
                count(*) as num
        from a
        group by order_phone_num
    ) a_num
    join
    (
        select count(*) as all_num
        from a
    ) a_all
    on 1 = 1

)

其中如果a_num有n行，a_all表示分组前的行数数字，肯定只有一条，它们join…on 1=1，结果还是n行，相当于在a_num中加上了一个常数列，这一列所有数据都是all_num，所以每一行的num都有一个all_num相对应，这样就可以用除法：num/all_num，计算百分比了