Início / dba / Perguntas / 291599
Accepted
ZedRed
Asked: 2021-05-15 12:42:49 +0800 CST

Executar cálculo não está funcionando na subconsulta, "não existe"

  • 772
PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, 
compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

Eu tenho uma consulta que fornece a contagem de 2 coisas em 2 períodos de tempo:

  • período desta semana, início da semana (domingo/segunda-feira meia-noite) -> hoje
  • período de tempo da semana passada, início da semana passada (meia-noite de domingo/segunda-feira) -> hoje mas semana passada

O que eu gostaria é de também realizar um cálculo nos dois ints de contagem de "carros" que são retornados e fornecer isso com o resultado da consulta.

É claro que posso fazer isso no aplicativo de consumo, mas adoraria poder fazer isso no meu SQL.

SQL de trabalho

Este é o SQL base e é o que tenho atualmente e trabalhando :

WITH last_week AS (
  SELECT COUNT(car_name) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_name) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

Configurar tabela de banco de dados

CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

Coisa que causa erro

Eu gostaria de adicionar o seguinte código - para calcular difference_between_weeks:

ROUND(car_count_current_week/(car_count_last_week/100) - 100)
    AS difference_between_weeks

Eu tentei calcular nas colunas, tentei adicionar outra subconsulta. Mas eu não pareço ser capaz de calcular o difference_between_weekscomo ele me diz "não existe" ouERROR: division by zero

SQL completo que erros

Um exemplo do SQL com o código adicionado que apresenta erros :

WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week,
  ROUND(car_count_current_week/(car_count_last_week/100)) - 100 AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

Erro retornado

Estou tendo o erro a seguir:

ERROR:  division by zero
SQL state: 22012

Sinto que estou perto, mas também sinto que isso pode não ser possível? Quaisquer indicações para o que estou perdendo seriam muito apreciadas.

postgresql postgresql-13

2 respostas

  1. Seu erro foi que o Postgres interpretou a coluna como Integer e realiza uma divisão euclidiana , que leva ao zero encontrado.

    Alterar o car_count_last_week para um decimal, fornecerá a divisão de números racionais

     (car_count_last_week::decimal/100)

    corrige o problema

    CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_date  TIMESTAMP,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

    INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');  

    WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  ,
  
 CASE WHEN car_count_last_week = 0 then 1
 ELSE ROUND(car_count_current_week/(car_count_last_week::decimal/100)) * 100 
 END 
   AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

    nome_carro | start_of_last_week | hoje_mas_última_semana | start_of_current_week | hoje | car_count_last_week | car_count_current_week | diferença_entre_semanas
:------- | :------------------ | :------------------ | :--------------------- | :------------- | ------------------: | ----------: | -----------------------:
incrível | 03-05-2021 00:00:00 | 09-05-2021 00:00:00 | 2021-05-10 00:00:00+01 | 2021-05-15 | 1 | 4 | 40.000

    db<>fique aqui

    • 2
  2. Best Answer

    Não vejo por que você precisa de um CROSS JOINneste caso! Eles são assassinos de desempenho!

    Você pode simplificar bastante esse problema fazendo o seguinte (e torná-lo mais geral na barganha!) - todo o código abaixo está disponível no violino aqui :

    CREATE TABLE IF NOT EXISTS car_store 
(
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_time TIMESTAMP(0) NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id),
     
     car_date DATE     GENERATED ALWAYS AS (car_time::DATE) STORED,
     car_week SMALLINT GENERATED ALWAYS AS (EXTRACT(WEEK FROM car_time)) STORED
);

    Observe o uso dos GENERATEDcampos (também conhecidos como "calculados" ou "virtuais"). O PG 12 apresentou isso e, como você está com 13 anos, somos de ouro!

    A implementação do PG tem apenas o STOREDtipo de armazenamento no momento, mas quando o VIRTUALtipo aparecer, provavelmente seria melhor neste caso - embora os campos sejam meros 6 bytes (4 para DATEe 2 para SMALLINT).

    Eu também adicionei dados - porque 6 registros não dão uma boa base para qualquer teste!

    Amostra:

    -- Week starting on Mon (19-04) and ending on Sunday (25-04) (poor sales)


INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-19 10:01:00');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-19 10:02:27'); 


INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-20 12:12:00');

-- no sales on Tuesday

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:05:27');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:06:00');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:07:27'); 
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-04-22 17:08:00');
..
..  ~ 60 more records snipped...
..

    O que eu fiz então foi:

    SELECT 
  cwk,
  c_name,
  LAG(wk_cnt, 1) OVER (PARTITION BY c_name ORDER BY cwk, c_name) AS last_wk_sales, 
  wk_cnt AS this_week_sales, 
  CASE
    WHEN LAG(wk_cnt, 1) OVER (PARTITION BY c_name ORDER BY cwk, c_name) IS NULL 
      THEN '*** No previous sales for period***'
    WHEN cwk = EXTRACT(WEEK FROM NOW())
      THEN 'Figures week to date'
    ELSE   'Figures valid'
  END AS status

FROM
(
  SELECT 
    car_week AS cwk,
    car_name AS c_name,
    COUNT(cs.car_week) AS wk_cnt
  FROM car_store cs
  GROUP BY car_week, car_name
  ORDER BY car_week
) AS tab
ORDER BY cwk DESC, c_name;

    Resultado:

    cwk c_name  last_wk_sales   this_week_sales   status
19  awesome            16                17   Figures week to date
18  awesome            22                16   Figures valid
17  awesome            12                22   Figures valid
16  awesome     NULL                     12   *** No previous sales for period***

    Observe o uso da função LAG() do PostgreSQL WINDOW - elas são muito poderosas e vale a pena conhecê-las - elas retribuirão o tempo e o esforço gastos em aprendê-las muitas vezes... LAG() é perfeito para o seu caso de uso onde você deseja para comparar as vendas da semana passada com as desta semana!

    Em seguida, adicionei cálculos para as porcentagens:

    SELECT 
  cwk,
  c_name,
  LAG(wk_cnt) OVER w AS last_wk_sales, 
  wk_cnt AS this_week_sales, 
  CASE
    WHEN LAG(wk_cnt) OVER w IS NULL 
      THEN '*** No previous sales for period***'
    WHEN cwk = EXTRACT(WEEK FROM NOW())
      THEN 'Figures week to date'
    ELSE   'Figures valid - period closed'
  END AS status,
  wk_cnt - LAG(wk_cnt) OVER w AS diff,
  ROUND(((wk_cnt - LAG(wk_cnt) OVER w)::REAL/LAG(wk_cnt) OVER w)*100) AS pc_change
FROM
(
  SELECT 
    car_week AS cwk,
    car_name AS c_name,
    COUNT(cs.car_week) AS wk_cnt
  FROM car_store cs
  GROUP BY car_week, car_name
  ORDER BY car_week
) AS tab
WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)
ORDER BY cwk DESC, c_name;

    Resultado (não formatado, exceto as duas últimas (novas) colunas):

    cwk c_name  last_wk_sales   this_week_sales status      diff     pc_change
19  awesome 16   17 Figures week to date                   1             6
18  awesome 22   16 Figures valid - period closed         -6           -27
17  awesome 12   22 Figures valid - period closed         10            83
16  awesome NULL 12 *** No previous sales for period***

    Então, temos nossa diferença em números absolutos e a variação percentual da semana anterior

    Eu também adicionei isso:

    WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)

    É outro método para arrumar seu SQL - você não precisa colocar essa WINDOWdefinição complicada em todo lugar - apenas um pouco de açúcar, mas é bom ter!

    Eu também cuidei do problema que o @nbk apontou com a divisão INTEGER - sim, pode enganar as pessoas, mas apenas aprenda sobre o problema e siga em frente!

      ROUND(((wk_cnt - LAG(wk_cnt) OVER w)::REAL/LAG(wk_cnt) OVER w)*100) AS pc_change

    Eu usei o REALtipo em vez de DECIMAL- daqui , são apenas 4 bytes e precisos de 6 casas decimais - dificilmente acho que mais do que isso será necessário para este caso de uso. Além disso, a partir dessa página:

    No entanto, os cálculos em valores numéricos são muito lentos em comparação com os tipos inteiros ou com os tipos de ponto flutuante descritos na próxima seção.

    Você pode se livrar dos CASE WHEN LAG(wk_cnt) OVER w IS NULLbits complicados colocando o lote em uma subconsulta da seguinte maneira:

    SELECT 
  *, 
  (TO_DATE(cyr::TEXT || cwk::TEXT, 'YYYYWW') + INTERVAL '3 DAY')::DATE AS "Week starting:"
FROM
(
  SELECT 
...
... body of query snipped - see fiddle - it is the same as above
...
  ) AS tab
  WINDOW w AS (PARTITION BY c_name ORDER BY cwk, c_name)
) AS tab2
WHERE last_wk_sales IS NOT NULL -- could also add dates between...
ORDER BY cwk DESC, c_name;

    Você pode nem precisar disso - se você sempre vender pelo menos um carro por semana - caso contrário, você pode JOINcom uma tabela de calendário, como mostrado aqui .

    Performance with the sub-query doesn't appear to suffer too much (see fiddle), although I would urge you to test various indexes with your own data and h/ware setup - although, with car sales, the number of records isn't likely to be huge.

    • 2

relate perguntas