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

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
     ),
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

（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
     )
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

（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

)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

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