只选择具有几个相同字段值的4条记录中的一条(selecting only one of 4 records that have several identical field values)

只选择具有几个相同字段值的4条记录中的一条(selecting only one of 4 records that have several identical field values)

我正在运行一个带有MySQL查询的脚本（如下所示）完全符合预期，但由于我们的数据存在问题，我需要进行一次小的更改。

这个查询的电话系统有时会为同一个电话插入3或4条记录，我们不知道为什么，但是当这个运行时，我们只需要为每个电话打一个记录。 我已经决定这需要通过查看不同的Calling_number, Start_Time, End_time, and 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;

I'm running a script with a MySQL query (seen below) That works exactly as expected but I need to make one small change due to an issue with our data.

The phone system that this is querying sometimes inserts 3 or 4 records for the same phone call, and we don't know exactly why, but when this runs we only want one record for each phone call. I've decided that this needs to be done by looking at a distinct Calling_number, Start_Time, End_time, and Talk_Time_Seconds. The duplicated/identical rows have the same values for those three fields.

The query itself is exactly what we want so I don't want to have to restructure it much if possible, but I need some help with this.

How can I make this select a distinct record if calling number, start, end and talk time are exactly the same value as stated above?

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;

最满意答案

尝试在四列上使用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;

Try using row_number partition by on the four columns :

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;