How to add more columns to make a sum over partition?-CodePudding

There is a query making a sum over partition by the date part of a timestamp column :

String sql = "select TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END || e.prenomemploye as user, "   
                "to_char(h.connexion, 'DD/MM/YYYY HH24:MI:SS') as connex, "   
                "to_char(h.deconnexion, 'DD/MM/YYYY HH24:MI:SS') as deconnex, "   
                "to_char(CAST(h.duree_seconde || ' second' AS INTERVAL), 'HH24:MI:SS') as duree, "   
                "to_char(CAST(SUM(h.duree_seconde) OVER (PARTITION BY CAST(h.connexion AS DATE)) || ' second' AS INTERVAL), 'HH24:MI:SS') duree_totale_par_date "   
                "from employe e join utilisateur u on e.idemploye = u.idemploye "  
                "join histodureeconnexion h on h.iduser = u.idutilisateur ";

I want to make the sum to be also grouped by TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END || e.prenomemploye. How to achieve that ?

CodePudding user response：

I don't know this query work for you or not, Because you don't add any record or sample. Please check and notice response about the result

select TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END ||
       e.prenomemploye                                                       as user,
       to_char(h.connexion, 'DD/MM/YYYY HH24:MI:SS')                         as connex,
       to_char(h.deconnexion, 'DD/MM/YYYY HH24:MI:SS')                       as deconnex,
       to_char(CAST(h.duree_seconde || ' second' AS INTERVAL), 'HH24:MI:SS') as duree,
       to_char(CAST(SUM(h.duree_seconde) OVER (PARTITION BY CAST(h.connexion AS DATE)) || ' second' AS INTERVAL),
               'HH24:MI:SS')                                                    duree_totale_par_date,
       SUM(h.duree_seconde) OVER (PARTITION BY TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END || e.prenomemploye) AS duree_totale_par_user
from employe e
         join utilisateur u on e.idemploye = u.idemploye
         join histodureeconnexion h on h.iduser = u.idutilisateur

Or you can use CTE:

WITH data AS (
    select TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END ||
           e.prenomemploye                                                       as user,
           to_char(h.connexion, 'DD/MM/YYYY HH24:MI:SS')                         as connex,
           to_char(h.deconnexion, 'DD/MM/YYYY HH24:MI:SS')                       as deconnex,
           to_char(CAST(h.duree_seconde || ' second' AS INTERVAL), 'HH24:MI:SS') as duree,
           to_char(CAST(SUM(h.duree_seconde) OVER (PARTITION BY CAST(h.connexion AS DATE)) || ' second' AS INTERVAL),
                   'HH24:MI:SS')                                                    duree_totale_par_date
    from employe e
             join utilisateur u on e.idemploye = u.idemploye
             join histodureeconnexion h on h.iduser = u.idutilisateur)
SELECT *, sum(duree_seconde) OVER (PARTITION BY "user") AS duree_totale_par_user
FROM data;

CodePudding user response：

Ok, @Pooya gave me inspiration, so here is the query :

SELECT histo.users, 
to_char(histo.connexion, 'DD/MM/YYYY HH24:MI:SS') as connex, 
to_char(histo.deconnexion, 'DD/MM/YYYY HH24:MI:SS') as deconnex, 
to_char(CAST(hISTO.dureeseconde || ' second' AS INTERVAL), 'HH24:MI:SS') as duree, 
to_char(CAST(SUM(histo.dureeseconde) OVER (PARTITION BY CAST(histo.connexion AS DATE) ORDER BY histo.users) || ' second' AS INTERVAL), 'HH24:MI:SS') duree_totale_par_date 
FROM (
    select TRIM(e.nomemploye) || CASE WHEN e.nomemploye IS NULL OR TRIM(e.nomemploye) = '' THEN '' ELSE ' ' END || e.prenomemploye as users,
           h.id as pk, h.connexion, h.deconnexion, h.duree_seconde as dureeseconde 
    from employe e
             join utilisateur u on e.idemploye = u.idemploye
             join histodureeconnexion h on h.iduser = u.idutilisateur) histo 
order by histo.users, histo.pk desc;