这是一条 SQL 语句，看起来比较长。以下是一些可以考虑优化的建议：
1. 将表名、字段名等缩短，以减少字符数和提高可读性。
2. 将复杂的表连接操作拆分成多个子查询，以降低查询复杂度。
3. 对于一些计算操作，可以考虑使用内置函数或自定义函数，以简化代码并提高可维护性。
4. 对于一些重复的操作，可以将其提取成变量或常量，以减少重复代码。
综上所述，优化后的 SQL 可能类似于以下代码（仅供参考）：
```
-- 定义常量
SET adjust_days = 1;
-- 查询订单表
WITH order_data AS (
SELECT
o.order_bill_id,
o.counterfoil_no,
o.acceptor,
DATE_FORMAT(TO_UTC_TIMESTAMP(CAST(o.expiry_date AS BIGINT), 'GMT-8'), 'YYYY-MM-dd') AS expiry_date,
o.cert_no,
o.company_name,
o.third_order_id,
o.counterfoil_amt / 10000 AS counterfoil_amt,
o.transaction_amt / 10000 AS transaction_amt,
o.rate,
'3bp' AS service_tariffing,
((DATEDIFF(TO_UTC_TIMESTAMP(o.expiry_date, 'GMT-8'), TO_UTC_TIMESTAMP(o.transaction_date, 'GMT-8')) + adjust_days) * 0.0003 * (o.counterfoil_amt)) / 360 AS service_fee,
360 AS total_days,
DATEDIFF(TO_UTC_TIMESTAMP(o.expiry_date, 'GMT-8'), TO_UTC_TIMESTAMP(o.transaction_date, 'GMT-8')) + adjust_days AS modulation_date,
o.order_status_code,
o.order_status_msg,
o.tenant_id,
TO_UTC_TIMESTAMP(CAST(o.create_date AS BIGINT), 'GMT-8') AS create_date,
TO_UTC_TIMESTAMP(CAST(o.update_date AS BIGINT), 'GMT-8') AS update_date
FROM discountdw.ods_adds_order_bill_inc o
WHERE o.dt = '2023-06-06' AND o.channel_id = 101110004
),
-- 查询公司表
company_data AS (
SELECT
c.company_id,
c.cert_no,
c.tenant_id
FROM mecdw.ods_company_full c
WHERE c.platform_id = 'sdpjw'
),
-- 查询产品表
product_data AS (
SELECT
p.partner_id,
p.product_id,
p.channel_type,
p.bank_name
FROM discountdw.dim_adds_product_full p
),
-- 查询费用记录表
fee_data AS (
SELECT
f.third_id,
f.fee_amt,
f.status
FROM mecdw.dwd_sc_fee_record_full f
WHERE f.dt = '2023-06-06' AND f.biz_type = 2
),
-- 查询订单扩展信息表
order_ext_data AS (
SELECT
e.order_bill_id,
e.revenue
FROM discountdw.ods_sd_order_ext_inc e
WHERE e.dt = '2023-06-06'
),
-- 查询节假日表
holiday_data AS (
SELECT
h.civic_holiday
FROM sdpjwdw.dim_holiday_info_full h
)
-- 合并数据
INSERT OVERWRITE TABLE discountdw.dwd_sd_adds_order_bill_inc PARTITION(dt = '2023-06-06')
SELECT
o.order_bill_id,
o.counterfoil_no,
o.acceptor,
o.expiry_date,
c.company_id,
o.cert_no,
o.company_name,
o.third_order_id,
o.counterfoil_amt,
o.transaction_amt,
o.rate,
o.service_tariffing,
o.service_fee,
o.total_days,
o.modulation_date,
p.channel_type,
p.bank_name,
DATE_FORMAT(TO_UTC_TIMESTAMP(CAST(o.transaction_date AS BIGINT), 'GMT-8'), 'YYYY-MM-dd'),
o.order_status_code,
o.order_status_msg,
f.fee_amt,
f.status,
o.tenant_id,
e.revenue,
o.create_date,
o.update_date
FROM order_data o
LEFT JOIN company_data c ON o.cert_no = c.cert_no AND o.tenant_id = c.tenant_id
LEFT JOIN product_data p ON o.partner_id = p.partner_id AND o.product_id = p.product_id
LEFT JOIN fee_data f ON o.order_bill_id = f.third_id
LEFT JOIN order_ext_data e ON o.order_bill_id = e.order_bill_id
LEFT JOIN holiday_data h ON o.expiry_date = DATE_FORMAT(TO_UTC_TIMESTAMP(h.civic_holiday, 'GMT-8'), 'YYYY-MM-dd');
```
300
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
