1. 有交车日，无车架号问题
select * from(select contractno,frameno,deliveryenddate,canceldate,credate,operate_type,updatetime,load_time,row_number() over(partition by contractno order by updatetime desc,credate desc,load_time desc) rnfrom dl.tg_tact_vhc_order_tb)aawhere rn=1 and frameno='' and deliveryenddate<>'' and canceldate='' and operate_type<>'D'order by credate desc,updatetime desc,load_time desc;
2. 库存
with rst_data as (
select *
from (select a.dlrcode,
a.vhcnamecode,
a.model,
a.sfx,
a.color,
a.frameno,
a.operate_type,
concat(decode(nvl(a.supplier,''),'',a.dlrcode,a.supplier),a.frameno) dlr_frameno,
if(nvl(a.acceptdate,'')='','9999-99-99',substring(a.acceptdate, 1, 10)) acceptdate,
if(nvl(a.salesdate,'')='','9999-99-99',substring(a.salesdate, 1, 10)) salesdate,
row_number() over(partition by orderno,frameno order by updatetime desc,load_time desc) rn
from dl.tg_tact_vhc_ledjer_tb a where dlrcode<>'10112' and acceptdlrcode<>'10112'
-- where cast(batch_id as string ) not like '22%'
-- and length(cast(batch_id as string )) = 9
)aa
where rn = 1 and operate_type<>'D' and dlr_frameno <>''),
rst_data1 as
(select
if(nvl(salesdate,'')='','9999-99-99',substring(salesdate, 1, 10)) salesdate,
operate_type,
concat(nvl(t.dealercode,''),nvl(t.frameno,'')) dlr_frameno,
if(nvl(salescanceldate,'')='','9999-99-99',substring(salescanceldate, 1, 10)) salescanceldate,
row_number() over(partition by contractno order by updatetime desc,load_time desc) rn
from dl.tg_tact_vhc_order_tb t
-- where cast(batch_id as string ) not like '22%'
-- and length(cast(batch_id as string )) = 9
),
rst_data2 as --已销售并且未取消销售或者取消销售日期大于当天
(select distinct dlr_frameno from rst_data1 tb
where salesdate<= '2022-03-30' and salescanceldate > '2022-03-30'
and rn=1 and operate_type<>'D' and dlr_frameno <>'')
select count(1)
from (select a.dlr_frameno
from rst_data a
left join rst_data2 t2
on a.dlr_frameno = t2.dlr_frameno
where a.acceptdate <= '2022-03-30'
and a.salesdate > '2022-03-30'
and t2.dlr_frameno is null
group by a.dlr_frameno
--减去已销售
-- and not exists
-- (select dlr_frameno
-- from rst_data2 t2
-- where a.dlr_frameno = t2.dlr_frameno)
) t ;
库存2：
with rst_data as (
select concat(dlr, frameno) dlr_frameno,
frameno,
if(nvl(acceptdate, '') = '', '9999-99-99', substring(acceptdate, 1, 10)) acceptdate,
if(nvl(salesdate, '') = '', '9999-99-99', substring(salesdate, 1, 10)) salesdate
from (select a.dlrcode,
a.frameno,
a.operate_type,
a.acceptdate,
a.salesdate,
if(nvl(a.supplier, '') in ('','00000'),a.dlrcode,a.supplier) as dlr,
row_number()
over(partition by orderno,frameno order by updatetime desc,load_time desc) rn
from dl.tg_tact_vhc_ledjer_tb a
where load_time >= '2017-01-01 00:00:00'
-- and dlrcode<>'10112' and acceptdlrcode<>'10112'
-- where cast(batch_id as string ) not like '22%'
-- and length(cast(batch_id as string )) = 9
) aa
where rn = 1
and operate_type <> 'D'
and acceptdate >= '2017-01-01 00:00:00'),
rst_data1 as (
select concat(nvl(dealercode, ''), nvl(frameno, '')) dlr_frameno,
if(nvl(salesdate, '') = '', '9999-99-99', substring(salesdate, 1, 10)) salesdate,
if(nvl(salescanceldate, '') = '', '9999-99-99', substring(salescanceldate, 1, 10)) salescanceldate
from (select dealercode,
frameno,
salesdate,
salescanceldate,
contractdate,
canceldate,
operate_type,
row_number() over(partition by contractno order by updatetime desc,load_time desc) rn
from dl.tg_tact_vhc_order_tb t
where load_time >= '2017-01-01 00:00:00'
-- where cast(batch_id as string ) not like '22%'
-- and length(cast(batch_id as string )) = 9
) c
where rn = 1
and operate_type <> 'D'
and (salescanceldate >= '2017-01-01 00:00:00' or contractdate >= '2017-01-01 00:00:00' or
canceldate >= '2017-01-01 00:00:00')),
rst_data2 as --已销售并且未取消销售或者取消销售日期大于当天
(select distinct dlr_frameno
from rst_data1 tb
where salesdate <= '2022-04-25'
and salescanceldate > '2022-04-25')
select count(1)
from (select a.dlr_frameno
from rst_data a
left join rst_data2 t2
on a.dlr_frameno = t2.dlr_frameno
where a.acceptdate <= '2022-04-25'
and a.salesdate > '2022-04-25'
-- and a.dlr_frameno is not null
and t2.dlr_frameno is null
group by a.dlr_frameno)t;
--减去已销售
-- and not exists
-- (select dlr_frameno
-- from rst_data2 t2
-- where a.dlr_frameno = t2.dlr_frameno)t;
dms:
# 店头库存（包含退订车辆，未销售车辆）
SELECT d.allot_original_dealer_code as "经销店代码",
d.allot_object_dealer_code as "所属经销店",
d.allot_status as "调拨标识",
d.allot_time as "调拨日",
c.customer_order_number as '客户订单',
o.dvp_order_number as '厂家订单',
a.car_owner_name as "购车人名称",
a.car_owner_cert_number as "身份证号",
a.unified_social_credit_code as "社会统一信用代码",
o.vehicle_name_code as "车辆名称代码",
o.model_code as 车型代码,
o.vehicle_sfx_code as "SFX",
r.vinno as "车架号",
o.vehicle_color_code as "颜色代码",
o.grade_code as "等级代码",
o.grade_name as "等级名称",
o.exhaust as "排气量",
o.gearbox as "变速箱",
c.input_date as '录入日',
c.gen_date as "生成日",
r.outday_date as "出门日",
c.sales_date as "销售日",
c.delivery_date as "交车日",
b.dist_time as '分配日',
r.newest_pickup_date as "接车日",
o.create_time as "创建时间"
FROM t_dvp_order o
LEFT JOIN t_dvp_order_ledger_relation r ON o.id = r.dvp_order_id
and o.del_status = 0 and o.del_flag = 0 and r.del_flag = 0
left join t_vhs_order_distribute b on b.mfr_order_number = o.dvp_order_number and b.del_flag = 0
left join t_vhs_order c on c.customer_order_number = b.customer_order_number and c.del_flag = 0
left join t_vhs_order_car_owner a on a.customer_order_number = c.customer_order_number and b.del_flag = 0
left join t_allot_info d on d.dvp_order_id = o.id and d.del_flag = 0
WHERE
# r.outday_date IS NOT NULL
c.sales_date is null
and r.del_flag = 0
and o.allowance_date is not null
and d.del_flag = 0
and o.del_flag = 0
and o.dealer_code <> '10112'
and c.unsub_date is null
and o.cancel_date is null
AND r.newest_pickup_date IS NOT NULL;
3. 未到店
with rst_data as
(select *
from (select
a.dealercode,
a.model,
a.sfx,
a.color,
a.orderno,
a.vhcnamecode,
a.frameno,
a.operate_type,
if(nvl(a.dealerallowancedate,'')='','9999-99-99',substr(a.dealerallowancedate, 1, 10)) dealerallowancedate,
if(nvl(a.acceptdate,'')='','9999-99-99',substr(a.acceptdate, 1, 10)) acceptdate,
row_number() over(partition by orderno order by updatetime desc,load_time desc) rn
from dl.tg_tact_snd_orderno_tb a where load_time >= '2017-01-01 00:00:00'
)aa
where rn = 1 and aa.operate_type <> 'D')
select count(1) wdd
from (select
a.dealercode, a.vhcnamecode, a.sfx, a.color, a.frameno
from rst_data a
where a.dealerallowancedate<='2022-03-30'
and a.acceptdate>'2022-03-30'
) t
未到店差异
with rst_data as
(select *
from (select
a.dealercode,
a.model,
a.sfx,
a.color,
a.orderno,
a.vhcnamecode,
a.frameno,
a.operate_type,
if(nvl(a.dealerallowancedate,'')='','9999-99-99',substr(a.dealerallowancedate, 1, 10)) dealerallowancedate,
if(nvl(a.acceptdate,'')='','9999-99-99',substr(a.acceptdate, 1, 10)) acceptdate,
row_number() over(partition by orderno order by updatetime desc,load_time desc) rn
from dl.tg_tact_snd_orderno_tb a
where cast(batch_id as string ) like '22%'
and length(cast(batch_id as string )) = 9
)aa
where rn = 1 and aa.operate_type <> 'D'),
aaa as(
select
a.dvp_order_number,
if(nvl(a.allowance_date,'')='','9999-99-99',substr(a.allowance_date, 1, 10)) allowance_date,
if(nvl(b.accept_date,'')='','9999-99-99',substr(b.accept_date, 1, 10)) accept_date
from ods.ods_dms_cs_dvp_order_collect a
join ods.ods_dms_cs_vhcl_ledger_collect b
on a.dvp_order_number=b.dvp_order_number
where a.del_flag<>1)
select a.dvp_order_number,b.orderno from
(select dvp_order_number from aaa
where aaa.allowance_date<='2022-04-20'
and aaa.accept_date>'2022-04-20')a
full join
(select orderno
from rst_data a
where a.dealerallowancedate<='2022-04-20'
and a.acceptdate>'2022-04-20'
)b on a.dvp_order_number = b.orderno
where a.dvp_order_number is null
or b.orderno is null
;
4. 汉得sfx统计异常问题
select t.sfx,
count(t.contractno) as amount2
from (select * ,row_number() over(partition by contractno order by updatetime desc,load_time desc) rn
from dl.tg_tact_vhc_order_tb ) t
where SUBSTR(canceldate,1,7)='2022-03'
and t.salespart not in ('5', '6')
-- and t.salesdate is not null
and nvl(t.salesdate,'')<>''
and rn=1
and operate_type<>'D'
and t.sfx in (
'C2AC',
'C2AD',
'C2AE',
'C2AF',
'C2AJ',
'C2AK',
'C2AL',
'C2AQ',
'C5AA',
'C5AB',
'C5AC',
'C5AD',
'C5AE',
'C5AF',
'C5AJ',
'C5AK',
'C5AL')
group by t.sfx order by t.sfx;
5.销售,新增,退订
dmp:
-- 新增
select substr(contractdate ,1,10),count(distinct contractno) sum
from (
select *,row_number() over(partition by contractno order by updatetime desc,load_time desc)rn from dl.tg_tact_vhc_order_tb
)as a
where substr(contractdate ,1,10)>="2022-02-01"
and dealercode <> "10112"
and SALESPART <> '5'
and operate_type<>"D"
and rn =1 group by substr(contractdate ,1,10) ;
-- 销售
-- 销售因子1
select substr(salesdate ,1,10),count(distinct contractno) sum
from (
select *,row_number() over(partition by contractno order by updatetime desc,load_time desc)rn from dl.tg_tact_vhc_order_tb
)as a
where substr(salesdate ,1,10)>="2022-02-01"
and contractdate <>""
and dealercode <> "10112"
and operate_type<>"D"
and rn =1 group by substr(salesdate ,1,10);
-- 销售因子2
select substr(salescanceldate ,1,10),count(distinct contractno) sum
from (
select *,row_number() over(partition by contractno order by updatetime desc,load_time desc)rn from dl.tg_tact_vhc_order_tb
)as a
where substr(salescanceldate ,1,10)>="2022-02-01"
and dealercode <> "10112"
and operate_type<>"D"
and salesdate<>""
and rn = 1 group by substr(salescanceldate ,1,10);
-- 退订
select substr(canceldate ,1,10),count(distinct contractno) sum
from (
select *,row_number() over(partition by contractno order by updatetime desc,load_time desc)rn from
( select * from dl.tg_tact_vhc_order_tb
where cancel<>''
and contractdate<>''
and operate_type<>'D'
and dealercode<>'10112'
and substr(canceldate ,1,10)>="2022-02-01") as a
)as a
where rn = 1 group by substr(canceldate ,1,10);
dms:
-- 销售
select substring(sales_date,1,10),count(distinct customer_order_number) sum from t_vhs_order tvo
where substring(sales_date,1,10)>='2022-02-01'
and dealer_code <> '10112'
and gen_date is not null
and delete_flag <>1 group by substring(sales_date,1,10);
select substring(unsub_date,1,10),count(distinct customer_order_number) sum from t_vhs_order tvo
where substring(unsub_date,1,10)>='2022-02-01'
and sales_date is not null
and dealer_code <> '10112'
and gen_date is not null
and delete_flag <>1 group by substring(unsub_date,1,10);
-- 新增
select substring(gen_date,1,10),count(distinct customer_order_number) sum from t_vhs_order tvo
where substring(gen_date,1,10)>='2022-02-01'
and dealer_code <> '10112'
and delete_flag <>1 group by substring(gen_date,1,10);
-- 退订
select count(distinct customer_order_number) sum from t_vhs_order tvo
where substring(cancel_date,1,10)='2022-02-28'
or substring(unsub_date,1,10)='2022-02-28'
and dealer_code <> '10112'
and delete_flag <>1 ;
6.出门日
dmp:
select outdaydate,count(distinct urn)
from (select substr(Outdaydate,1,10) outdaydate,operate_type,urn,
row_number() over(partition by orderno
order by updatetime desc ,load_time desc)rn from dl.tg_tact_snd_orderno_tb)aa
where outdaydate between '2022-04-12' and '2022-04-12'
and operate_type<> 'D'
and rn =1
group by outdaydate;
dms:
select outday_date, count(DISTINCT urn)
from (select substr(outday_date,1,10) outday_date,urn,
del_flag,
row_number() over (partition by urn order by update_time desc)rn
from t_vhcl_ledger where ifnull(receive_dealer_code, '') <> '10112')
where outday_date between '2022-04-01' and '2022-04-14'
and del_flag =0 and rn=1
group by outday_date;
7.引当日
dmp:
select dealerallowancedate, count(DISTINCT urn)
from (select substr(DealerAllowanceDate,1,10) dealerallowancedate,updatetime,load_time,urn,operate_type,
row_number() over(partition by orderno order by updatetime desc,load_time desc) rn
from dl.tg_tact_snd_orderno_tb
where cast(batch_id as string ) like '22%'
and length(cast(batch_id as string )) = 9
)aa
where rn=1
and dealerallowancedate >= '2022-04-01'
AND operate_type <> 'D'
GROUP BY dealerallowancedate;
dms:
select allowance_date, count(distinct urn)
from (
select * from (
select dvp_order_number,
substr(allowance_date,1,10) allowance_date ,
del_flag,
row_number() over (partition by dvp_order_number order by update_time desc)rn
from ods.ods_dms_cs_dvp_order where ifnull(dealer_code,'')<>'10112'
)aa
where rn=1 and del_flag=0) o
join ( select * from (
select dvp_order_number,
urn ,
del_flag,
row_number() over (partition by dvp_order_number order by update_time desc,create_time desc)rn
from ods.ods_dms_cs_dvp_order_ledger_relation )aa
where rn=1 and del_flag=0) r
on o.dvp_order_number = r.dvp_order_number
where
allowance_date >= '2022-04-01'
GROUP BY allowance_date;
8.终检日
dmp:
select finaltestfinishdate, count(DISTINCT urn)
from (select substr(Finaltestfinishdate,1,10) finaltestfinishdate,urn,operate_type,
row_number() over(partition by orderno order by
updatetime desc,load_time desc)rn from dl.tg_tact_snd_orderno_tb)a
where finaltestfinishdate >= '2022-04-01'
and operate_type<>'D'
and rn=1
group by finaltestfinishdate;
dms:
select actual_final_check_date, count(DISTINCT a.urn)
from (select * from (
select substr(actual_final_check_date,1,10) actual_final_check_date,urn,del_flag,
row_number() over (partition by urn,vinno order by update_time desc)rn
from ods.ods_dms_cs_vhcl_check)aa where rn=1 and del_flag=0) a
join (select * from (
select urn,vinno,
del_flag,
row_number() over (partition by urn,vinno order by update_time desc)rn
from ods.ods_dms_cs_vhcl_ledger where ifnull(receive_dealer_code, '') <> '10112'
)aa where rn=1 and del_flag=0) b
on a.urn = b.urn
where actual_final_check_date >= '2022-04-01'
group by actual_final_check_date;
9.到店日
dmp:
select driveracceptime, count(DISTINCT urn)
from (select substr(driveracceptime,1,10) driveracceptime,
urn,
operate_type,
row_number() over(partition by orderno order by updatetime desc,load_time desc)rn
from dl.tg_tact_snd_orderno_tb
where cast(batch_id as string ) like '22%'
and length(cast(batch_id as string )) = 9
)a
where driveracceptime >= '2022-04-01'
and operate_type<>'D'
and urn <>''
and rn=1
group by driveracceptime;
dms:
select newest_pickup_date, count(distinct urn)
from (
select * from (
select dvp_order_number,
del_flag,
row_number() over (partition by dvp_order_number order by update_time desc)rn
from t_dvp_order where ifnull(dealer_code,'')<>'10112'
)aa
where rn=1 and del_flag=0) o
join ( select * from (
select dvp_order_number,
urn ,
substr(newest_pickup_date,1,10) as newest_pickup_date,
del_flag,
row_number() over (partition by dvp_order_number order by update_time desc)rn
from t_dvp_order_ledger_relation )aa
where rn=1 and del_flag=0) r
on o.dvp_order_number = r.dvp_order_number
where newest_pickup_date between'2022-04-01' and '2022-04-14'
GROUP BY newest_pickup_date;
10.入款确认日
select substr(a.dealer_confirm_date, 1, 10), count(distinct b.urn)
# distinct b.urn
from (select *
from (
select dvp_order_number,
dealer_confirm_date,
del_flag,
row_number() over (partition by dvp_order_number order by update_time desc,create_time desc ) rn
from t_dvp_order
where ifnull(dealer_code, '') <> '10112') aa
where del_flag <> 1 and rn=1) a
join
(select *
from (select dvp_order_number,
urn,
del_flag,
row_number() over (partition by dvp_order_number order by update_time desc,create_time desc ) rn
from t_dvp_order_ledger_relation) bb
where del_flag <> 1 and rn=1) b
on a.dvp_order_number = b.dvp_order_number
where
-- 销售店申请日期 在统计范围内
substr(a.dealer_confirm_date, 1, 10) between '2022-02-01' and '2022-04-26'
GROUP BY substr(a.dealer_confirm_date, 1, 10);
11. 销售店申请日
dms:
select request_date, count(distinct a.dvp_order_number)
-- select distinct urn
from
(select *
from (
select dvp_order_number,
del_flag,
substr(request_date,1,10) as request_date,
row_number() over (partition by dvp_order_number order by update_time desc,create_time desc ) rn
from t_dvp_order
where ifnull(dealer_code, '') <> '10112'
) aa
where del_flag <> 1 and rn=1
) a
where
-- 销售店申请日期 在统计范围内
request_date between '2022-04-01' and '2022-05-23'
GROUP BY request_date;
12. 测试ODS引当日，接车日是否写入旧表
with rst_data as
(select *
from (select
a.dealercode,
a.model,
a.sfx,
a.color,
a.orderno,
a.vhcnamecode,
a.frameno,
a.operate_type,
if(nvl(a.dealerallowancedate,'')='','9999-99-99',substr(a.dealerallowancedate, 1, 10)) dealerallowancedate,
if(nvl(a.acceptdate,'')='','9999-99-99',substr(a.acceptdate, 1, 10)) acceptdate,
row_number() over(partition by orderno order by updatetime desc,load_time desc) rn
from dl.tg_tact_snd_orderno_tb a
where cast(batch_id as string ) like '22%'
and length(cast(batch_id as string )) = 9
)aa
where rn = 1 and aa.operate_type <> 'D'),
aaa as(
select
a.dvp_order_number,
if(nvl(a.allowance_date,'')='','9999-99-99',substr(a.allowance_date, 1, 10)) allowance_date,
if(nvl(b.accept_date,'')='','9999-99-99',substr(b.accept_date, 1, 10)) accept_date,
a.biz_date as biz_date1,b.biz_date as biz_date2,b.id as vehicle_id
from ods.ods_dms_cs_dvp_order_collect a
join ods.ods_dms_cs_vhcl_ledger_collect b
on a.dvp_order_number=b.dvp_order_number
where a.del_flag<>1)
select a.vehicle_id,a.dvp_order_number,a.allowance_date,a.accept_date ,a.biz_date1,a.biz_date2,c.biz_date as biz_date3,
b.orderno,b.dealerallowancedate,b.acceptdate from
(select * from aaa
-- where aaa.allowance_date<='2022-04-20'
-- and aaa.accept_date>'2022-04-20'
)a
full join
(select orderno,dealerallowancedate,acceptdate
from rst_data a
-- where a.dealerallowancedate<='2022-04-20'
-- and a.acceptdate>'2022-04-20'
)b on a.dvp_order_number = b.orderno
join ods.ods_dms_cs_vhcl_accept_dealer c on a.vehicle_id=c.vehicle_id
-- where a.dvp_order_number is null
-- or b.orderno is null
where a.allowance_date<> b.dealerallowancedate
or a.accept_date<>b.acceptdate
or b.orderno is null
;
13.国地补
SELECT
dlrcode AS dlrcode,
vin AS vin,
saleTime AS saleTime,
batch_id AS batchId,
licensePlate AS licensePlate,
vehTypeName AS vehTypeName,
saleArea AS saleArea,
ownerName AS ownerName,
Idtype AS Idtype,
Idnum AS Idnum,
epname AS epname,
epaddress AS epaddress,
epcode AS epcode,
deleteFlag AS deleteFlag,
updateCount AS updateCount,
creDate AS creDate,
creUser AS creUser,
updateTime AS updateTime,
updateUser AS updateUser,
mttime AS mttime,
serial AS serial,
mtkind AS mtkind,
operate_type AS operateType,
load_time AS loadTime
FROM (SELECT *,
row_number()
over(PARTITION BY vin,saleTime ORDER BY batch_id DESC)
rownum
FROM dl.TG_TACT_VHC_EVSALESINFO_F
WHERE
CAST(load_time AS timestamp)>=date_sub(to_date(now()),0)
)a
WHERE rownum=1;
select * from dl.tg_dms_sal_t_vhs_national_standard order by load_time desc limit 10000;
若有收获，就点个赞吧
0 人点赞
