瞬时月度统计数据

1. 统计瞬时总数， boss 文件

瞬时营销解析入库(15分钟) 这个就是超套 -- 原始文件:SEND_HN02001_731，  /hn/JinFen/CT00001/  HN_JF_CT00001_ 这是回传文件

流量高饱和度号码入库  原始文件：SEND_HN02002_731 , 回传文件 ：HN_JF_DLXS00001_

达量限速  原始文件：SEND_HN02104_731

瞬时营销超套0元  原始文件：SEND_HN02012_731_

瞬时营销超套3元  原始文件：SEND_HN02012_731_

以上文件在148.77上去找。ftp_url = "10.154.148.77" ，ftp_user_name = "bankftp"，ftp_user_pwd = "Bankp_07"

upload_path = '/app/bankftp/online/hour/'

外呼样本数：
```
select IN_DATETIME,from_channel,count(SERIALNUMBER) from  tb_activity_product_list 
where from_channel in ('2','5','3') and IN_DATETIME like '202306%'
group by IN_DATETIME,from_channel
```

清洗数：
```
select substring(add_dateTime,1,8), from_channel,count(SERIALNUMBER) from tb_activity_product_list_filter
where from_channel in ('2','5','3') and  substring(add_dateTime,1,8) like '202306%'
group by substring(add_dateTime,1,8), from_channel
```

2-5元超套，3-达量限速，5-高饱和度，21-0元超套，22-3元超套

大数据平台里 :TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_731_DAY

相关sql
```
select IN_DATETIME,from_channel ,count(SERIALNUMBER) from  tb_activity_product_list 
where from_channel in ('2','3','5','21','22') and IN_DATETIME like '202306%' and has_pushed='1'
group by IN_DATETIME,from_channel
order by IN_DATETIME ,from_channel

select substring(add_dateTime,1,8), from_channel,count(SERIALNUMBER) from tb_activity_product_list_filter
where from_channel in ('2','3','5','21','22') and  substring(add_dateTime,1,8) like '202306%'
group by substring(add_dateTime,1,8), from_channel

from_channel ='5' 高饱和

select SERIALNUMBER,has_pushed,from_channel,task_id,push_id from  tb_activity_product_list 
 where from_channel in ('21') and IN_DATETIME like '202306%'

select * from  tb_activity_product_list_main
  where from_channel in ('21') and IN_DATETIME like '202306%' and SERIALNUMBER ='18274098288'

select IN_DATETIME,from_channel ,count(SERIALNUMBER) from  tb_activity_product_list 
where from_channel in ('22') and IN_DATETIME like '202306%' and  task_id is  null
group by IN_DATETIME,from_channel

select IN_DATETIME,from_channel ,count(SERIALNUMBER)from  tb_activity_product_list 
where from_channel in ('2','3','5','21','22') and IN_DATETIME like '202306%'  and task_id is not null
group by IN_DATETIME,from_channel
```

大数据平台上:

TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY

TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY

--------------------------------------------------------------------------------

瞬时已入库:

```
select substring (statis_date ,1, 6 ) , count(serialnumber) as shujuliang , count(distinct serialnumber) as kehushu
from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY
where substring (statis_date ,1, 6 ) in ('202404','202405')
group by substring (statis_date ,1, 6 )
瞬时已入库 ，数据量和客户数
按天:
select statis_date , count(serialnumber) as shujuliang , count(distinct serialnumber) as kehushu
from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY
where statis_date  in (
'20240520','20240521','20240522','20240523','20240524','20240525','20240526','20240527','20240528','20240529','20240530','20240531',
'20240620','20240621','20240622','20240623','20240624','20240625','20240626','20240627','20240628','20240629','20240630'
)
group by statis_date
------------------------------------------------------------------------
select substring (statis_date ,1, 6 ) , count(serialnumber) as shujuliang , count(distinct serialnumber) as kehushu
from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY
where substring (statis_date ,1, 6 ) in ('202404','202405') and from_channel in ('2','21','22','23','24','25')
group by substring (statis_date ,1, 6 )
只有 超套035的瞬时已入库 ，数据量和客户数

按天统计：
select statis_date , count(serialnumber) as shujuliang , count(distinct serialnumber) as kehushu
from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY
where statis_date  in (
'20240520','20240521','20240522','20240523','20240524','20240525','20240526','20240527','20240528','20240529','20240530','20240531',
'20240620','20240621','20240622','20240623','20240624','20240625','20240626','20240627','20240628','20240629','20240630'
)   and  from_channel in ('2','21','22') 
group by statis_date

---------------------------------------

select substring (statis_date ,1, 6 ) , count(serialnumber) as shujuliang , count(distinct serialnumber) as kehushu ,

sum (
case 
  when has_pushed ='1' then 1
  else 0
  end
) as pushed ,

sum (
case 
  when has_pushed ='0' then 1
  else 0
  end
) as not_push

from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY
where substring (statis_date ,1, 6 ) in ('202404','202405')  and  from_channel in ('2','21','22')  
group by substring (statis_date ,1, 6 )
加上-- 超套 0,3,5 已推送，未推送的量 
-------------------------------------------------------
select substring (statis_date ,1, 6 ) , has_pushed , count(serialnumber) as shujuliang , count(distinct serialnumber) as kehushu

from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY
where substring (statis_date ,1, 6 ) in ('202404','202405')     
group by substring (statis_date ,1, 6 ) , has_pushed
加上 是否已推送

```

------------------

未推送（去重已推送的手机号）

```
select count ( distinct b.serialnumber ) from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY b
where substring(b.statis_date,1,6 )='202404' and b.from_channel in ('2','21','22') and b.has_pushed ='0'

and b.serialnumber not in (

select distinct a.serialnumber from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY a
where substring(a.statis_date,1,6 )='202404' and a.from_channel in ('2','21','22') and a.has_pushed ='1'

)

未推送 （按时间排列）
select b.statis_date , count ( distinct b.serialnumber ) from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY b
where 
statis_date in ('20240501','20240502','20240503','20240504','20240505','20240506','20240507','20240508',
'20240509','20240510','20240601','20240602','20240603','20240604','20240605','20240606','20240607','20240610','20240608','20240609')
and b.from_channel in ('2','21','22') and b.has_pushed ='0'

and b.serialnumber not in (

select distinct a.serialnumber from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY a
where 
  statis_date in ('20240501','20240502','20240503','20240504','20240505','20240506','20240507','20240508',
'20240509','20240510','20240601','20240602','20240603','20240604','20240605','20240606','20240607','20240610','20240608','20240609')
  and a.from_channel in ('2','21','22') and a.has_pushed ='1'

)

group by  b.statis_date

```
清洗数:( 清洗数 =  filter表里的手机号去重 排除 入库表里的手机号)
```
 select count (distinct c.serialnumber  )
             from csap731.TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY c  
         		  where substring(c.statis_date,1,6 )='202404' and c.from_channel in ('2','21','22')
          
          and c.serialnumber not in (
          
            select distinct b.serialnumber from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY b
					where substring(b.statis_date,1,6 )='202404'  and b.from_channel in ('2','21','22') 
          
           )
  清洗数 按日期排列的:         
 select c.statis_date , count (distinct c.serialnumber  )
             from csap731.TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY c  
         		  where c.statis_date  in (
                  '20240511','20240512','20240513','20240514','20240515','20240516','20240517','20240518','20240519',
  '20240611','20240612','20240613','20240614','20240615','20240616','20240617','20240618','20240619'
                  ) and c.from_channel in ('2','21','22')
          
          and c.serialnumber not in (
          
            select distinct b.serialnumber from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY b
					where b.statis_date in ('20240511','20240512','20240513','20240514','20240515','20240516','20240517','20240518','20240519',
  '20240611','20240612','20240613','20240614','20240615','20240616','20240617','20240618','20240619'
                    
                    )  and b.from_channel in ('2','21','22') 
          
           )
 group by c.statis_date
```

清洗原因: (filter表中数据去重，排除已入库的手机号 再连接一个原因)  ， 加入一个 row_number 只取一行数据

```

select e.backups ,count(1) FROM

(
select distinct a.serialnumber , d.backups   from 
csap731.TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY a

left join (  select distinct c.serialnumber , c.backups ,  row_number()over( partition by c.serialnumber) as rownumber 
             from csap731.TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY c  
          where substring(c.statis_date,1,6 )='202404' and c.from_channel in ('2','21','22')
          ) d on  d.serialnumber =  a.serialnumber and d.rownumber ='1'

where substring(a.statis_date,1,6 )='202404' and a.from_channel in ('2','21','22') 
and  a.serialnumber not in (
select distinct b.serialnumber from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY b
where substring(b.statis_date,1,6 )='202404'  and b.from_channel in ('2','21','22')

)  
) e
group by e.backups

----------------------------------------------------

用日期排列 ：

select e.statis_date , e.backups ,count(1) FROM

(
select distinct a.statis_date , a.serialnumber , d.backups   from 
csap731.TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY a

left join (  select distinct c.serialnumber , c.backups ,  row_number()over( partition by c.serialnumber) as rownumber 
             from csap731.TB_RP_CT_TB_ACTIVITY_PRODUCT_LIST_FILTER_731_DAY c  
          where 
           
            c.statis_date in ('20240501','20240502','20240503','20240504','20240505','20240506','20240507','20240508',
                  '20240509','20240510','20240601','20240602','20240603','20240604','20240605','20240606','20240607','20240610','20240608','20240609')
           
           and c.from_channel in ('2','21','22') and c.statis_date = a.statis_date
           
          ) d on  d.serialnumber =  a.serialnumber and d.rownumber ='1'

where 
   a.statis_date in ('20240501','20240502','20240503','20240504','20240505','20240506','20240507','20240508',
                  '20240509','20240510','20240601','20240602','20240603','20240604','20240605','20240606','20240607','20240610','20240608','20240609')
  
  and a.from_channel in ('2','21','22') 
and  a.serialnumber not in (
select distinct b.serialnumber from csap731.TB_DW_CCSYS_TB_ACTIVITY_PRODUCT_LIST_731_DAY b
where 
   b.statis_date in ('20240501','20240502','20240503','20240504','20240505','20240506','20240507','20240508',
                  '20240509','20240510','20240601','20240602','20240603','20240604','20240605','20240606','20240607','20240610','20240608','20240609')
  and b.from_channel in ('2','21','22')

)  
) e
group by e.statis_date, e.backups
```