请以最详细的方式解释这段代码with a01 as( select sm_id, isname, case when isname in ('冰箱', '冷柜') then '制冷' when isname = '商空' then '楼宇' when isname = '家空' then '空调' else isname end as cyx, factory_code, mtname, curstatus, model_code, model_name, start_time, --'保养起始时间' expire_time, --'保养到期时间' substring(start_time, 1, 7) byqsny, substring(expire_time, 1, 7) byjssj, case when stype = '2' then '二保' when stype = '3' then '三保' else stype end as bylx, case when mm_type = '2' then '二保' when mm_type = '3' then '三保' when mm_type = '4' then '三保带二保' else mm_type end as mmtype, --实际保养类型 upper_time, --上次保养时间 mm_time, --实际保养时间 scustcode, -- 实供应商编码 scustname, --实供应商名称 mould_status, --模具维保状态 mm_status, --实际是否保养 smould_status --模具实际维保状态 from dwd_mm_staymaintain_all a where by_status <> '无需保养' and length(factory_code) = 4 and curstatus not in ('报废中', '已报废', '冻结数据', '垃圾数据') and isname in ('冰箱', '冷柜', '家空', '商空', '洗涤', '厨电', '热水器') and substring(expire_time, 1, 10) <= from_unixtime(unix_timestamp(), 'yyyy-MM-dd') and expire_time is not null and expire_time <> '' ), b01 as( select cyx, byjssj, count(sm_id) by_ying from a01 group by cyx, byjssj ), b02 as( select cyx, byjssj, count(sm_id) by_shi from a01 where mm_status = '已保养' group by cyx, byjssj ),c01 as( select from_unixtime(unix_timestamp(), 'yyyy-MM') months, t1.cyx industry, t1.byjssj byjsyf, cast(t1.by_ying as string) by_ying, cast(t2.by_shi as string) by_shi, cast(t2.by_shi/t1.by_ying as decimal(8,5)) bywcl from b01 t1 left join b02 t2 on t1.cyx = t2.cyx and t1.byjssj = t2.byjssj) insert overwrite table dh_yf.tt_mould_asset_maintenance_overview select months, industry, byjsyf, by_ying, by_shi, cast(bywcl as string) bywcl, from_unixtime(unix_timestamp(), 'yyyy-MM-dd') etl_date, '%' unit from c01
时间: 2023-11-28 16:03:56 浏览: 144
这段代码是使用 SQL 语言编写的一个查询操作,主要是对一个名为 dwd_mm_staymaintain_all 的表进行查询,并将查询结果插入到名为 dh_yf.tt_mould_asset_maintenance_overview 的表中。具体来说,这段代码可以分为以下几个部分:
1. WITH 子句
代码的第一行开始是 WITH 子句,用于创建临时表 a01,其语法为:
```
with a01 as (
select sm_id, isname, case when isname in ('冰箱', '冷柜') then '制冷' when isname = '商空' then '楼宇' when isname = '家空' then '空调' else isname end as cyx, factory_code, mtname, curstatus, model_code, model_name, start_time, expire_time, substring(start_time, 1, 7) byqsny, substring(expire_time, 1, 7) byjssj, case when stype = '2' then '二保' when stype = '3' then '三保' else stype end as bylx, case when mm_type = '2' then '二保' when mm_type = '3' then '三保' when mm_type = '4' then '三保带二保' else mm_type end as mmtype, upper_time, mm_time, scustcode, scustname, mould_status, mm_status, smould_status
from dwd_mm_staymaintain_all a
where by_status <> '无需保养' and length(factory_code) = 4 and curstatus not in ('报废中', '已报废', '冻结数据', '垃圾数据') and isname in ('冰箱', '冷柜', '家空', '商空', '洗涤', '厨电', '热水器') and substring(expire_time, 1, 10) <= from_unixtime(unix_timestamp(), 'yyyy-MM-dd') and expire_time is not null and expire_time <> ''
)
```
- WITH 子句中,我们使用 AS 关键字将临时表命名为 a01。
- 在括号内,我们使用 SELECT 语句来定义 a01 的列,这些列可以来自一个或多个表,以及任何需要的 WHERE 子句来筛选数据。
2. 两个临时表 b01 和 b02
代码的第九行和第十九行是临时表 b01 和 b02 的定义,分别用于计算应保养和实际保养的数量,并将结果存储到这两个临时表中。具体语法如下:
```
with
b01 as (
select cyx, byjssj, count(sm_id) by_ying
from a01
group by cyx, byjssj
),
b02 as (
select cyx, byjssj, count(sm_id) by_shi
from a01
where mm_status = '已保养'
group by cyx, byjssj
)
```
3. 临时表 c01
代码的第二十九行到第三十六行是临时表 c01 的定义,用于计算保养完成率。具体语法如下:
```
with
c01 as (
select from_unixtime(unix_timestamp(), 'yyyy-MM') months, t1.cyx industry, t1.byjssj byjsyf,
cast(t1.by_ying as string) by_ying, cast(t2.by_shi as string) by_shi,
cast(t2.by_shi/t1.by_ying as decimal(8,5)) bywcl
from b01 t1
left join b02 t2 on t1.cyx = t2.cyx and t1.byjssj = t2.byjssj
)
```
- WITH 子句中,我们使用 AS 关键字将临时表命名为 c01。
- 在括号内,我们使用 SELECT 语句来定义 c01 的列,这些列是通过从临时表 b01 和 b02 中选择计算结果来计算的。
4. INSERT 语句
代码的最后一行是 INSERT 语句,用于将临时表 c01 中的结果插入到名为 dh_yf.tt_mould_asset_maintenance_overview 的表中。具体语法如下:
```
insert overwrite table dh_yf.tt_mould_asset_maintenance_overview
select months, industry, byjsyf, by_ying, by_shi,
cast(bywcl as string) bywcl, from_unixtime(unix_timestamp(), 'yyyy-MM-dd') etl_date, '%' unit
from c01
```
- INSERT 语句用于将 SELECT 语句的结果插入到指定的表中。
- 在 SELECT 语句中,我们选择了 c01 中的所有列,并将它们插入到 dh_yf.tt_mould_asset_maintenance_overview 表中的相应列中。
总体来说,这段代码的作用是从 dwd_mm_staymaintain_all 表中选择符合特定条件的数据,并计算保养完成率,最后将结果插入到 dh_yf.tt_mould_asset_maintenance_overview 表中。
阅读全文