每日sql-累计求和与插入数据到新表

目录

• 1 sum()voer() 累计求和
• 2 select * into 将数据插入到新表

1、sum（）over（）累计求和

sum(列名) over (partition by <用于分组的列名>               
                order by <用于排序的列名>)

• over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()，sum()等一起使用。
• over函数的参数：over（partition by columnname1 order by columnname2）
  含义，按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
  题目来源：牛客网
  image.png
  解读：要统计截止到最近日期用户刷题通过的总数，所以用sum（）over（）滚动求和。这样一来，在passing_number中就要按用户id和date分别与login连接，即有两个连接条件。

select u.name as u_n, c.name as c_n, p.date as date, 
sum(number) over (partition by p.user_id order by p.date) as ps_num
from login l 
join user u on u.id = l.user_id
join client c on c.id = l.client_id
join passing_number p on p.user_id = l.user_id and p.date = l.date
order by p.date, u.name

举一反三：
来源：猴子数据分析

image.png

select 雇员编号,薪水,
sum(薪水) over (order by 雇员编号) as 累计薪水
from 薪水表
where 结束日期 = '9999-01-01';

partition by用户分组，牛客网的题目是说按照每个登陆者进行统计，累计求和。而这个题目不需要分组，所以不用partiton by。
拓展练习：row_number()over() 只需要统计行数
来源：猴子数据分析

image.png
条件一：线上订单量占比>50%
条件二: 需要知道线上订单数

select a.*, b.经纪人所在门店的线上占比,
        case when  成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
        then  null else 1  end as  是否线上,
        row_number ()  over (partition by  a.签约经纪人门店名 order by a.签约时间) 
        as 单量
from  成交订单表 as a
left join
(select 签约经纪人门店名称,
      count(case when  成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
        then  null else 1  end )
 / count(*) as 经纪人所在门店的线上占比
from
成交订单表
group by 签约经纪人门店名称) as b 
on a.签约经纪人门店名称 = b.签约经纪人门店名称
where
b.经纪人所在门店的线上占比 > 0.5
and month  (a.签约时间) = month(now())
and a.房屋用途 = ""

然后，把上面的表作为临时表c，分配奖励

select c.*,
case when c.单量>= 3  then 800
     when c.单量>= 2  then 400
     else 200 end  as  该单应发的贝壳币   
from 
(select *,
row_number() 
     over  (partition by 签约经纪人门店名称 
            order by 签约时间 ) as 单量
from 成交订单表) as c;

完整代码：

select  c.*, 
      case when c.单量>= 3  then 800
      when c.单量>= 2  then 400
      else 200 end  as  该单应发的贝壳币 
from
(select a.*, b.经纪人所在门店的线上占比,
        case when  成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
        then  null else 1  end as  是否线上,
  row_number () 
   over (partition by  a.签约经纪人门店名称
        order by a.签约时间) as 单量
from  成交订单表 as a
left join
(select 签约经纪人门店名称,
      count(case when  成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
        then  null else 1  end )
 / count(*) as 经纪人所在门店的线上占比
from
成交订单表
group by 签约经纪人门店名称) as b 
on a.签约经纪人门店名称 = b.签约经纪人门店名称
where
b.经纪人所在门店的线上占比 > 0.5
and month  (a.签约时间) = month(now())
and a.房屋用途 = "") as c;

2、SELECT INTO 从一个表中选取数据，然后把数据插入另一个表中。

INSERT INTO 语句用于向表格中插入新的行。

INSERT INTO table_name VALUES (值1, 值2,....)

指定所要插入数据的列：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

把所有的列插入新表

SELECT *
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

只把希望的列插入新表

SELECT column_name(s)
INTO new_table_name [IN externaldatabase] 
FROM old_tablename

题目来源：某打车公司将驾驶里程（drivedistanced）超过5000里的司机信息转移到一张称为seniordrivers 的表中,他们的详细情况被记录在表drivers 中，正确的sql为（）

select * into seniordrivers from drivers where drivedistanced >=5000