How to join two rows in SELECT?

1

I have a problem with joining 2 lines. I researched a lot, but found nothing that could solve my case.

In the query below, there are two SELECTs, which return two rows for each extension, as shown below. In the code, I need to merge these two lines to display on screen, but this merge in the code is causing a lot of problems.

Does anyone have any idea how I can return this result in just one line? For example:

| 2016-04-15 | 2200 | Bruna Farias da Silva Neves | 1 | 454 | 18 | 470 |

Note: I need to group by date and by extension.

   ( SELECT     date(cdr.calldate) as data, 
            cdr.dst as ramal, 
            users.name as nome, 
            count(date(cdr.calldate)) as qtde_entrante, 
            sum(cdr.duration) as tempo_entrante, 
            NULL as qtde_sainte, 
            NULL as tempo_sainte 
    FROM ('asteriskcdrdb'.'cdr') 
        JOIN 'asterisk'.'users' ON 'users'.'extension' = 'cdr'.'dst' 
    WHERE cdr.calldate >= '2016-04-15 00:00:00' 
    and cdr.calldate <='2016-04-15 23:59:59' 
    AND cdr.dst LIKE '220%' 
    GROUP BY date(cdr.calldate), cdr.dst) 
 UNION ALL 
 ( SELECT   date(cdr.calldate) as data, 
            cdr.src as ramal, 
            users.name as nome, 
            NULL as qtde_entrante, 
            NULL as tempo_entrante, 
            count(date(cdr.calldate)) as qtde_sainte, 
            sum(cdr.duration) as tempo_sainte 
    FROM ('asteriskcdrdb'.'cdr') 
        JOIN 'asterisk'.'users' ON 'users'.'extension' = 'cdr'.'src' 
    WHERE cdr.calldate >= '2016-04-15 00:00:00' 
    and cdr.calldate <='2016-04-15 23:59:59' 
    AND cdr.src LIKE '220%' 
    AND cdr.dst not in ('s') 
    AND cdr.dst not like '*2%' 
    AND (cdr.dst < 900 OR cdr.dst > 999) 
    GROUP BY date(cdr.calldate), cdr.src) 
    ORDER BY date(data) asc, ramal asc)

    
asked by anonymous 15.04.2016 / 14:18

4 answers

1

You can do this with SQL, but you'll end up experiencing slowness.

In code, the no-compromise performance solution is to pick up incoming and outgoing data separately, pick up all extensions from people who had calls on those days, and do the JOIN manually.

The SQL below attempts to replicate these steps.

SELECT A.data
     , B.extension
     , B.name
     , C.qtde_entrante
     , C.tempo_entrante
     , D.qtde_sainte
     , D.tempo_sainte
FROM   ( SELECT DISTINCT DATE(calldate) AS data
         FROM   cdr
         WHERE  calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59'
       ) AS A
CROSS JOIN
       ( SELECT extension , name
         FROM users
         WHERE extension IN ( SELECT src FROM cdr where calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59' )
          OR   extension IN ( SELECT dst FROM cdr where calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59' )
       ) AS B
LEFT OUTER JOIN
       ( SELECT date(calldate) as data
              , dst as ramal
              , count(date(calldate)) as qtde_entrante
              , sum(duration) as tempo_entrante
         FROM   cdr
         WHERE  calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59'
          AND   dst LIKE '220%'
         GROUP BY 1,2
       ) AS C ON ( A.data = C.data AND B.extension = C.ramal )
LEFT OUTER JOIN
       ( SELECT date(calldate) as data
              , src as ramal
              , count(date(calldate)) as qtde_sainte
              , sum(duration) as tempo_sainte
         FROM   cdr
         WHERE  calldate BETWEEN '2016-04-15 00:00:00' AND '2016-04-15 23:59:59'
          AND   src LIKE '220%'
          AND   dst not in ('s')
          AND   dst not like '*2%'
          AND   (dst < 900 OR dst > 999)
         GROUP BY 1,2
       ) AS D ON ( A.data = D.data AND B.extension = D.ramal )
WHERE ( NOT qtde_entrante IS NULL ) AND ( NOT qtde_sainte IS NULL )
    
15.04.2016 / 22:27
1

Separate into two sub-queries

Your question is incomplete for further testing, but I believe the way is for you to create two sub-queries and then join with the users. A SELECT that represents the incoming calls (where the extension is connected to the dst field) and another that represents outgoing calls (where the extension is connected to the src field).

By understanding their output, these queries should be joined by the date and extension, ie where both the date and the extension number have incoming and outgoing call records. After putting these two views together you would join the user table using the extension field.

I understand that there may be incoming calls on dates that do not have outgoing calls, and vice versa, so JOIN could be FULL OUTER JOIN , however you need to analyze if you want to get these situations in your result. In the case of an outer join you also need to bind to the user table twice, one for dst and one for src .

As an illustration (because I can not test) the query below can help you understand what I am proposing and make necessary adjustments to the final result.

As a hint of performance issues, this is likely to be with LIKE . Try to reshape the base to allow these filters to use simple expressions ( =, >, < )

SELECT 
    date(entrada.calldate) as data, 
    entrada.ramal,
    users.name as nome,  
    entrada.qtde_e, 
    entrada.duracao_e, 
    saida.qtde_s, 
    saida.duracao_s
FROM
    (
        SELECT  cdr.src as ramal,
                cdr.calldate,
                COUNT(cdr.calldate) qtde_s,
                SUM(cdr.duration) as duracao_s
        FROM asteriskcdrdb.cdr AS cdr
        WHERE cdr.calldate >= '2016-04-15 00:00:00' 
        and cdr.calldate <= '2016-04-15 23:59:59' 
        AND cdr.src LIKE '220%' 
        AND cdr.dst not in ('s') 
        AND cdr.dst not like '*2%' 
        AND (cdr.dst < 900 OR cdr.dst > 999) 
        GROUP BY cdr.calldate, cdr.src
    ) AS saida
JOIN
    (
        select  cdr.dst as ramal,
                cdr.calldate,
                count(cdr.calldate) as qtde_e, 
                sum(cdr.duration) as tempo_e
        FROM asteriskcdrdb.cdr as cdr
        WHERE cdr.calldate >= '2016-04-15 00:00:00' 
        and cdr.calldate <= '2016-04-15 23:59:59' 
        AND cdr.dst LIKE '220%' 
        GROUP BY cdr.calldate, cdr.dst
    ) AS entrada
ON entrada.ramal = saida.ramal 
AND entrada.calldate = saida.calldate
JOIN asterisk.users as users ON users.extension = entrada.ramal
    
03.04.2017 / 14:05
0

Use SELECT DISTINCT. The function already filters the field that would perform the select, I believe reassembling your want using it can make it easier. link ;

    
02.06.2017 / 21:13
0

Hello, you may be able to leave UNION and try selects that resolve the counts for you within the initial query, for example:

SELECT     date(cdr.calldate) as data, 
            cdr.dst as ramal, 
            users.name as nome, 
            count(date(cdr.calldate)) as qtde_entrante, 
            sum(cdr.duration) as tempo_entrante, 
            (SELECT COUNT(date(cdr.calldate)) FROM asteriskcdrdb.cdr WHERE [..outros filtros..]) as qtde_sainte, 
            (SELECT COUNT(cdr.duration) FROM asteriskcdrdb.cdr WHERE [.. outros filtros..]) as tempo_sainte 
    FROM (asteriskcdrdb.cdr) 
        JOIN asterisk.users ON users.extension = cdr.dst 
    WHERE cdr.calldate >= '2016-04-15 00:00:00' 
    and cdr.calldate <='2016-04-15 23:59:59' 
    AND cdr.dst LIKE '220%' 
    GROUP BY date(cdr.calldate), cdr.dst) 
    
02.06.2017 / 21:49