mysql - 仅选择具有多个相同字段值的 4 条记录之一

Question

我正在运行一个带有 MySQL 查询的脚本(如下所示)，该脚本完全按照预期工作，但由于我们的数据存在问题，我需要进行一个小更改。

此查询的电话系统有时会为同一个电话插入 3 或 4 条记录，我们不知道确切原因，但当它运行时，我们只想为每个电话插入一条记录。我认为这需要通过查看不同的 Calling_number、Start_Time、End_time 和 Talk_Time_Seconds 来完成。重复/相同的行在这三个字段中具有相同的值。

查询本身正是我们想要的，因此如果可能的话，我不想对其进行太多重组，但我需要一些帮助。

如果调用号码、开始、结束和通话时间与上述值完全相同，如何才能选择不同的记录？

      SELECT
          FirstN
          , LastN
          , Extension
          , Recieved
          , Recieved_Known
          , Outbound
          , Outbound_Known
          , Missed_No_VM
          , Missed_VM
          , Missed_Known
          , Calling_Number
          , Called_Number
          , Start_Time
          , End_Time
        , Talk_Time_Seconds
          , Hold_Time_Seconds

        FROM (
            SELECT
             firstn
             , lastn
             , c.extension
             , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved
             , case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as Recieved_Known
             , CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 then 'x' ELSE '' end  AS Outbound
             , case when LEGTYPE1 = 1 and FINALLYCALLEDPARTYNO = k.phone_number then 'x' ELSE '' end as Outbound_Known
             , case when legtype1 = 2 and answered = 0 and finallycalledpartyno  not like '%oice%' then 'x' ELSE '' end as Missed_No_VM
             , case when finallycalledpartyno like '%oice%' then 'x' ELSE '' end as Missed_VM
             , case when ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as Missed_Known
             , a.CALLINGPARTYNO AS Calling_Number
             , a.FINALLYCALLEDPARTYNO AS Called_Number
             , b.starttime as Start_Time
             , b.endtime as End_Time
             , b.duration as Talk_Time_Seconds
             , a.holdtimesecs as Hold_Time_Seconds

            FROM ambition.session a
            INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
            right join jackson_id.users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
            LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
                WHERE a.ts >= curdate()
                and(a.CALLINGPARTYNO in (select extension from ambition.ambition_users) OR a.finallycalledpartyno IN (select extension from ambition.ambition_users))
            ) x
            order by extension;

Answer 1

尝试在四列上使用 row_number 分区:

 SELECT
          FirstN
          , LastN
          , Extension
          , Recieved
          , Recieved_Known
          , Outbound
          , Outbound_Known
          , Missed_No_VM
          , Missed_VM
          , Missed_Known
          , Calling_Number
          , Called_Number
          , Start_Time
          , End_Time
        , Talk_Time_Seconds
          , Hold_Time_Seconds

        FROM (
            SELECT
             firstn
             , lastn
             , c.extension
             , CASE WHEN LEGTYPE1 = 2 AND ANSWERED = 1 THEN 'x' ELSE '' END AS Recieved
             , case when LEGTYPE1 = 2 and answered = 1 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as Recieved_Known
             , CASE WHEN ANSWERED = 1 AND LEGTYPE1 = 1 then 'x' ELSE '' end  AS Outbound
             , case when LEGTYPE1 = 1 and FINALLYCALLEDPARTYNO = k.phone_number then 'x' ELSE '' end as Outbound_Known
             , case when legtype1 = 2 and answered = 0 and finallycalledpartyno  not like '%oice%' then 'x' ELSE '' end as Missed_No_VM
             , case when finallycalledpartyno like '%oice%' then 'x' ELSE '' end as Missed_VM
             , case when ANSWERED = 0 and CALLINGPARTYNO = k.phone_number then 'x' ELSE '' end as Missed_Known
             , a.CALLINGPARTYNO AS Calling_Number
             , a.FINALLYCALLEDPARTYNO AS Called_Number
             , b.starttime as Start_Time
             , b.endtime as End_Time
             , b.duration as Talk_Time_Seconds
             , a.holdtimesecs as Hold_Time_Seconds
            row_number() over (partition by a.CALLINGPARTYNO,b.starttime,b.endtime,b.duration order by 1) rn 
            FROM ambition.session a
            INNER JOIN ambition.callsummary b ON a.NOTABLECALLID = b.NOTABLECALLID
            right join jackson_id.users c on a.callingpartyno = c.extension or a.finallycalledpartyno = c.extension
            LEFT JOIN ambition.known_numbers k ON a.callingpartyno = k.phone_number
                WHERE a.ts >= curdate()
                and(a.CALLINGPARTYNO in (select extension from ambition.ambition_users) OR a.finallycalledpartyno IN (select extension from ambition.ambition_users))
            ) x
            where rn = 1
            order by extension;