Sql - Merge select - Create Row if nonexistent record

Asked

Viewed 137 times

0

I’m willing to join two SELECT, but I am unsuccessful.

Has the following tables:

CREATE TABLE tb_producao
(
  cd_producao SERIAL,
  cd_setor integer,
  nr_quantidade numeric(12,2),
  ds_producao character varying(255)
);

CREATE TABLE tb_mapa_producao
(
  cd_mapa_producao SERIAL,
  cd_lote_producao_ordem_ticket integer,
  cd_setor integer,
  dt_inicio timestamp without time zone
);

CREATE TABLE tb_setor
(
  cd_setor SERIAL,
  ds_setor character varying(200) NOT NULL
);

CREATE TABLE tb_lote_producao_ordem_ticket
(
  cd_lote_producao_ordem_ticket SERIAL,
  cd_lote_producao_ordem integer,
  nr_quantidade numeric(12,0) DEFAULT 0
);

And the following SQL:

SELECT
    S.cd_setor as "cd_setor", 
    SUM(COALESCE(LPOT.nr_quantidade,0)) as "nr_total_ticket",
    SUM(CASE MP.fl_desativado WHEN TRUE THEN LPOT.nr_quantidade ELSE 0 END) as "nr_total_concluido",
    (   
        SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
        FROM    tb_producao P 
        WHERE   P.cd_setor = S.cd_setor
    ) as "nr_media_producao"
    ,date_trunc('day', CASE WHEN MP.dt_inicio IS NULL THEN now() ELSE MP.dt_inicio END) as "dt_producao"
FROM
    tb_setor S
    LEFT JOIN tb_mapa_producao MP ON S.cd_setor = MP.cd_setor
    LEFT JOIN tb_lote_producao_ordem_ticket LPOT ON LPOT.cd_lote_producao_ordem_ticket = MP.cd_lote_producao_ordem_ticket
WHERE
    (
        (date_trunc('day', MP.dt_inicio) = date_trunc('day', now()))
        OR MP.cd_mapa_producao IS NULL
    )
GROUP BY    1,5
UNION
    SELECT
        S.cd_setor as "cd_setor", 
        null,
        null,
        (   
            SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
            FROM    tb_producao P 
            WHERE   P.cd_setor = S.cd_setor
        ) as "nr_media_producao",
        now()
    FROM
        tb_setor S
ORDER BY 
    1,5

Upshot :

1   |   120 |   70  |   1000.00 |   "2015-10-14 00:00:00-03"
1   |       |       |   1000.00 |   "2015-10-14 11:02:52.183443-03"
2   |   120 |   70  |   870.00  |   "2015-10-14 00:00:00-03"
2   |       |       |   870.00  |   "2015-10-14 11:02:52.183443-03"
3   |   0   |   0   |   733.33  |   "2015-10-14 00:00:00-03"
3   |       |       |   733.33  |   "2015-10-14 11:02:52.183443-03"
4   |   0   |   0   |   850.00  |   "2015-10-14 00:00:00-03"
4   |       |       |   850.00  |   "2015-10-14 11:02:52.183443-03"
5   |   120 |   70  |   950.00  |   "2015-10-14 00:00:00-03"
5   |       |       |   950.00  |   "2015-10-14 11:02:52.183443-03"
6   |   120 |   110 |   900.00  |   "2015-10-14 00:00:00-03"
6   |       |       |   900.00  |   "2015-10-14 11:02:52.183443-03"
7   |       |       |   966.67  |   "2015-10-14 11:02:52.183443-03"
8   |   0   |   0   |   866.67  |   "2015-10-14 00:00:00-03"
8   |       |       |   866.67  |   "2015-10-14 11:02:52.183443-03"
9   |       |       |   690.00  |   "2015-10-14 11:02:52.183443-03"
10  |   0   |   0   |   600.00  |   "2015-10-14 00:00:00-03"
10  |       |       |   600.00  |   "2015-10-14 11:02:52.183443-03"

Expected result

1   |   120 |   70  |   1000.00 |   "2015-10-14 00:00:00-03"
2   |   120 |   70  |   870.00  |   "2015-10-14 00:00:00-03"
3   |   0   |   0   |   733.33  |   "2015-10-14 00:00:00-03"
4   |   0   |   0   |   850.00  |   "2015-10-14 00:00:00-03"
5   |   120 |   70  |   950.00  |   "2015-10-14 00:00:00-03"
6   |   120 |   110 |   900.00  |   "2015-10-14 00:00:00-03"
7   |   0   |   0   |   966.67  |   "2015-10-14 11:02:52.183443-03"
8   |   0   |   0   |   866.67  |   "2015-10-14 00:00:00-03"
9   |   0   |   0   |   690.00  |   "2015-10-14 11:02:52.183443-03"
10  |   0   |   0   |   600.00  |   "2015-10-14 00:00:00-03"

Goal

I want to calculate the productivity of the present day, but if there is no production on the day, you should only calculate the nr_media_producao of the sector and maintain the other fields with 0 or now().

Situation

With the UNION I’ve always managed to calculate the nr_media_producao, but when there is production in the day generates me duplicated records, ie would like a merge that of preference for the query that has production in the day.

OBS

I’d like to use the sqlfiddle to demonstrate the data, but it generates error when I try to compile. due to many lines.

sql postgresql

  • William, could you put what result wait? You have the Inserts of the tables?

  • @Jeangustavoprates edited putting expected result, as for the Insert I have, but there are many to sqlfiddle not supported, I’ll see where I can put.

  • pus in the Regex 101, but only for can it show.

1 answer

2


Explanation

You can use your select as the basis of a derived table, making it possible to use a filter to keep the other fields with 0 or now().

We use the function ROW_NUMBER() to account for how many rows are repeated (partitioned by ID, since it repeats itself), and I ordered that the columns that are not NULL have "priority" in sorting, in this case we use a derived table as the basis for another derived table.

At the end, it was only necessary to select the columns separately so that the count of the rows would not be displayed.

Query

 SELECT 
      CD_SETOR, 
      NR_TOTAL_TICKET, 
      NR_MEDIA_PRODUCAO,
      NR_TOTAL_CONCLUIDO
 FROM (
         SELECT *, ROW_NUMBER() OVER (PARTITION BY cd_setor ORDER BY NR_TOTAL_TICKET ASC) RN FROM (   
                SELECT
                    S.cd_setor as "cd_setor", 
                    SUM(COALESCE(LPOT.nr_quantidade,0)) as "nr_total_ticket",
                    SUM(CASE MP.fl_desativado WHEN TRUE THEN LPOT.nr_quantidade ELSE 0 END) as "nr_total_concluido",
                    (   
                        SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
                        FROM    tb_producao P 
                        WHERE   P.cd_setor = S.cd_setor
                    ) as "nr_media_producao"
                    ,date_trunc('day', CASE WHEN MP.dt_inicio IS NULL THEN now() ELSE MP.dt_inicio END) as "dt_producao"
                FROM
                    tb_setor S
                    LEFT JOIN tb_mapa_producao MP ON S.cd_setor = MP.cd_setor
                    LEFT JOIN tb_lote_producao_ordem_ticket LPOT ON LPOT.cd_lote_producao_ordem_ticket = MP.cd_lote_producao_ordem_ticket
                WHERE
                    (
                        (date_trunc('day', MP.dt_inicio) = date_trunc('day', now()))
                        OR MP.cd_mapa_producao IS NULL
                    )
                GROUP BY    1,5
                UNION
                    SELECT
                        S.cd_setor as "cd_setor", 
                        null,
                        null,
                        (   
                            SELECT  COALESCE((SUM(COALESCE(P.nr_quantidade,0)) / CASE WHEN count(P.cd_producao) = 0 THEN 1 ELSE count(P.cd_producao) END)::numeric(12,2),0) as "P.ds_media_producao"
                            FROM    tb_producao P 
                            WHERE   P.cd_setor = S.cd_setor
                        ) as "nr_media_producao",
                        now()
                    FROM
                        tb_setor S
                ORDER BY 
                    1,5 ) A ) B WHERE B.RN = 1

  • did not solve, as it excludes data from sector 7 and 9 on day 14. as it will be null as to ticket. but must be present in the result as I commented.

  • William, you can try again?

  • worked just had to change DESC for ASC in SELECT *, ROW_NUMBER() OVER (PARTITION BY cd_setor ORDER BY NR_TOTAL_TICKET). If possible could I comment explaining? I understood the result but not the means.

  • @Guilhermelautert, see if you’re feeling better.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.