时间戳

首页 > 解决方案 > 如何分组和加入第二个表

oracle - 如何分组和加入第二个表

问题描述

当我加入前三个表（ACTIVATIONS、customer、agent_dtl）时，我试图在这里加入 4 个表，我得到 4000 行计数，但如果我尝试加入第四个表（postpaid_summary），我得到的行数超过 10 万行。这是为什么？

我认为本月有问题TO_CHAR(TRUNC(a.packag_start_date, 'MONTH'), 'MON-YYYY')，如何使用 min(TIME_DAY_KEY) 获得 4000 行？

SELECT
    a.act_actdevice,
    a.act_phone_no,
    a.bi_account_id,
    a.packag_start_date,
    TO_CHAR(TRUNC(a.packag_start_date, 'MONTH'), 'MON-YYYY') AS PACKAG_START_DATE_MONTHYEAR,
    a.retailer_name,
    a.retailer_type,
    a.dms_id as "DSR/BPR_ID",
    a.dsr_name as "DSR/BPR_NAME",
    a.agent_type,
    a.distributor_id,
    a.distributor_name,
    a.SALES_DISTRICT,
    a.profileid,
    s.district,
    s.province,
    c.identification_number,
    c.account_type,
    c.account_status,
    c.activation_date,
    c.permanent_disconnection_date,
    c.temporary_disconnection_date,
    c.status_change_date,
    c.credit_limit,
    c.average_monthly_bill_amount,
    c.primary_packag_start__date,
    c.package_code,
    c.sales_channel,
    c.site_id,
    c.district_name,
    c.usage_arpu,
    c.bill_to_contact_name,
    min(p.TIME_DAY_KEY) as first_consumption_date 
FROM
    ACTIVATIONS a 
    left JOIN customer c on TO_CHAR(a.act_phone_no) = c.msisdn_voice 
    left JOIN agent_dtl s ON a.dms_id = s.agent_id 
    JOIN postpaid_summary p on a.act_phone_no = p.MSISDN 
where
    a.packag_start_date BETWEEN TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2020-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 
group by
    a.act_actdevice,
    a.act_phone_no,
    a.bi_account_id,
    a.packag_start_date,
    TO_CHAR(TRUNC(a.packag_start_date, 'MONTH'), 'MON-YYYY'),
    a.retailer_name,
    a.retailer_type,
    a.dms_id,
    a.dsr_name,
    a.agent_type,
    a.distributor_id,
    a.distributor_name,
    a.SALES_DISTRICT,
    a.profileid,
    s.district,
    s.province,
    c.identification_number,
    c.account_type,
    c.account_status,
    c.activation_date,
    c.permanent_disconnection_date,
    c.temporary_disconnection_date,
    c.status_change_date,
    c.credit_limit,
    c.average_monthly_bill_amount,
    c.primary_packag_start__date,
    c.package_code,
    c.sales_channel,
    c.site_id,
    c.district_name,
    c.usage_arpu,
    c.bill_to_contact_name,
    p.TIME_DAY_KEY

标签： oracle

解决方案

请根据要求使用以下查询，

SELECT  distinct a.act_actdevice,a.act_phone_no,a.bi_account_id, a.packag_start_date, TO_CHAR(TRUNC(a.packag_start_date, 'MONTH'), 'MON-YYYY') AS PACKAG_START_DATE_MONTHYEAR,
       a.retailer_name,a.retailer_type,a.dms_id as "DSR/BPR_ID",a.dsr_name as "DSR/BPR_NAME",a.agent_type,a.distributor_id,
       a.distributor_name,a.SALES_DISTRICT,a.profileid,s.district,s.province,
       c.identification_number, c.account_type,c.account_status,c.activation_date,c.permanent_disconnection_date,c.temporary_disconnection_date,
       c.status_change_date,c.credit_limit,c.average_monthly_bill_amount,c.primary_packag_start__date,c.package_code,c.sales_channel,
       c.site_id,c.district_name,c.usage_arpu,c.bill_to_contact_name,
       min(p.TIME_DAY_KEY) as first_consumption_date
   
FROM ACTIVATIONS a
left JOIN customer c
on TO_CHAR(a.act_phone_no) = c.msisdn_voice
left JOIN agent_dtl s
ON   a.dms_id = s.agent_id 
JOIN postpaid_summary p
on a.act_phone_no = p.MSISDN

 

where  a.packag_start_date BETWEEN TO_DATE('2020-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2020-05-31 23:59:59', 'YYYY-MM-DD HH24:MI:SS') 
     
group by a.act_actdevice,a.act_phone_no,a.bi_account_id, a.packag_start_date, TO_CHAR(TRUNC(a.packag_start_date, 'MONTH'), 'MON-YYYY'),
       a.retailer_name,a.retailer_type,a.dms_id,a.dsr_name,a.agent_type,a.distributor_id,
       a.distributor_name,a.SALES_DISTRICT,a.profileid,s.district,s.province,
    c.identification_number,c.account_type,c.account_status,c.activation_date,c.permanent_disconnection_date,c.temporary_disconnection_date,
     c.status_change_date,c.credit_limit,c.average_monthly_bill_amount,c.primary_packag_start__date,c.package_code,c.sales_channel,
       c.site_id,c.district_name,c.usage_arpu,c.bill_to_contact_name,
       p.TIME_DAY_KEY

推荐阅读