Perguntas[greatest-n-per-group](dba)

Tenho duas tabelas: A e B

UMA

B

O resultado que eu quero

Entre as coisas que tentei é a junção esquerda

select * 
from "A" 
  left join "B" on "B"."provider_id" = "A"."provider_id" 
order by "A"."date" desc;

Mas eu tenho os registros duplicados com base emstatus

Tabela messages:

Como selecionamos a primeira mensagem e a primeira resposta enviada em uma conversa ( conv_id) todos os dias?

Notas:

1. Pode haver muitos usuários.
2. Um único usuário pode enviar várias mensagens.
3. Este é um conjunto de dados de pessoas, e apenas duas pessoas estão conversando entre si.
4. Ao longo do dia, várias mensagens são trocadas.
5. A primeira mensagem pode ser definida pelo tempo mínimo de envio no dia 1 do primeiro usuário.
6. A primeira resposta pode ser definida como o tempo mínimo de envio no dia 1 do segundo usuário.

Tenho uma tabela assim:

mas isso contém apenas 3 dias. Preciso de uma tabela com datas de calendário completo, uma linha para cada dia do ano em que o valor seja o último disponível.
Por exemplo, todos os registros de 1º de janeiro de 2022 a 14 de janeiro têm valor 1,0 (com escopo para group_name'A').

Já tentei usar LAST_VALUE()mas não está funcionando.

WITH dates AS (
    
    SELECT 
        date::date
    FROM generate_series ( '2022-01-01'::timestamp, '2022-01-31'::timestamp, '1 day'::interval) date

), incomplete_table AS (
     SELECT * FROM (VALUES 
        ('2022-01-01'::date, 'a', 1),
        ('2022-01-15'::date, 'a', 0.5), 
        ('2022-01-31'::date, 'a', 0.2), 
        ('2022-01-02'::date, 'b', 0.1),
        ('2022-01-10'::date, 'b', 0.15),
        ('2022-01-20'::date, 'b', 0.15)
    ) AS t (date,group_name, value)
)
SELECT
    dates.date,
    group_name, 
    value,
    LAST_VALUE(value) OVER (ORDER BY dates.date DESC) as last_value_window
FROM dates
LEFT JOIN incomplete_table ON incomplete_table.date = dates.date
ORDER BY dates.date DESC;

Estou trabalhando com o servidor mariadb versão 10.6.4.

Esta é a minha definição de tabela:

CREATE TABLE `tmp_dba` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `case_id` bigint(20) DEFAULT NULL,
  `client_id` bigint(20) NOT NULL,
  `arrival` date DEFAULT NULL,
  `departure` date DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

Aqui estão alguns dados de exemplo:

INSERT INTO `tmp_dba` VALUES
(1,10,1000,'2018-10-02','2019-04-25'),
(2,10,1000,'2019-04-26','2019-05-01'),
(3,10,1000,'2019-05-02',NULL),
(4,20,2000,'2018-11-21',NULL),
(5,20,2001,'2018-11-21',NULL),
(6,20,2002,'2018-11-21',NULL),
(7,30,3000,'2019-03-04','2022-01-01'),
(8,30,3001,'2019-03-04','2022-01-01'),
(9,30,3002,'2019-03-04','2022-01-01'),
(10,30,3003,'2019-03-04','2022-01-01'),
(11,30,3004,'2019-03-04','2022-01-01');

O que eu gostaria de conseguir é determinar o MIN(chegada) de cada grupo de case_id e client_id e caso a partida não seja nula MAX(partida) deve ser exibida caso contrário nula.

Eu quero acabar com apenas uma linha por cliente fornecendo os dados mencionados acima.

Por exemplo, para case_id = 10, quero ver 1 linha assim: 10;1000;2018-10-02;NULL.

Para case_id = 20, o resultado deve ser 4 linhas, devido a 4 combinações diferentes de case_id e client_id.

Para case_id = 30 deve haver 5 linhas mostradas, devido a 5 combinações diferentes de case_id e client_id.

Devo estar fazendo algo errado ao usar group by.

Outras informações:

• Os dados são consecutivos no sentido de que um novo registro inserido terá um ID maior que o antigo. Também será um novo recorde - em relação ao anterior - no futuro, sempre. Isso não significa que o próximo registro sempre teria uma chegada no dia seguinte à última partida.
• Um novo registro na tabela para o mesmo cliente sempre definirá a data de saída do registro anterior. A saída de registro anterior nesse caso não pode ser nula.
• Quando não houver saída e o cliente estiver "in" parado, a saída será sempre nula.
• A partida deve ser menor que a chegada, a menos que a partida seja nula.
• A chegada não pode ser nula.

Isso é factível?

Solução

SELECT 
-- First solution, but wrong, GROUP_CONCAT(ID) avoids error below
-- ID, case_id, client_id, <-- only_full_group_by - error
GROUP_CONCAT(ID), case_id, client_id,
MIN(arrival) AS arrival,
IF(COUNT(departure) = COUNT(*), MAX(departure), NULL) AS departure
FROM tmp_dba
GROUP BY case_id, client_id
ORDER BY case_id, client_id

Muito obrigado por sua ajuda.

Steffi

Eu tenho a seguinte tabela em um banco de dados PostgreSQL

id | value_1 | value_2
-----------------------------
1  | 100     | 10
1  | NULL    | 20
2  | 100     | 20
3  | 100     | 12
4  | 100     | 16
5  | NULL    | 200
5  | 400     | 200
5  | 580     | 200

Gostaria de manter o registro com o valor máximo fornecido value_1e remover todos os outros registros duplicados (tenha o mesmo id) para que a consulta reduza a tabela para:

id | value_1 | value_2
-----------------------------
1  | 100     | 10
2  | 100     | 20
3  | 100     | 12
4  | 100     | 16
5  | 580     | 200

Tenho uma consulta que retorna algumas linhas tendo um agrupamento em uma coluna e datas. Eu gostaria que essa consulta indicasse em uma coluna qual de cada grupo tem a maior data.

Há um grande número de perguntas sobre como fazer o "maior n por grupo", mas essas perguntas querem apenas a linha de cada grupo com o maior valor. Eu quero todas as linhas, e apenas uma indicação de qual é a maior.

Por exemplo, aqui está uma tabela:

Eu gostaria de ter algo como o seguinte:

Eu experimentei alguns métodos de maior n por grupo para adicionar um indicador "maior". Mas como essas tentativas são focadas na única linha com o maior valor, quando a parte da cláusula where como "table2.date IS NULL" é removida, linhas extras são adicionadas com todas as combinações de junção.

Aqui está um exemplo de um experimento que não funciona. Ele tenta usar o maior n-por-grupo, mas comenta a linha IS NULL:

SELECT DISTINCT m1.usermessageid, m1.repid, m1.sender_role, m1.message, m1.datesent, m2.datesent
FROM usermessage m1
LEFT JOIN usermessage m2 ON m1.principalid = m2.principalid and m1.repid = m2.repid AND m1.datesent < m2.datesent
WHERE m1.principalid = 99831
AND m1.repid IN (21600,99214,27058,97360,92678,96184,55429,46029)
# AND m2.datesent IS NULL
ORDER BY m1.datesent desc;

Gostaria de evitar ter que adicionar uma consulta adicional apenas para obter as maiores linhas.

Aqui está o esquema de amostra para meu banco de dados:

Priority  | Productive | UnProductive | Neutral |
-------------------------------------------------
  High    |   [1, 2]   |      []      |  [4, 5] |
  Medium  |   [3, 4]   |    [5, 7]    |   [2]   |
  Low     |     [1]    |    [2, 6]    |    []   |

NOTA: Esses números são na verdade UUIDs no meu banco de dados e nenhum número será repetido na mesma linha.

Eu quero desaninhar a categoria de productive, unproductivee de neutraltal forma que:

• Nenhum número de 1 a 7 é repetido e
• Se o número já foi capturado por High Priority não será capturado por Medium e o mesmo vale para Low .

Saída esperada:

Productive: [1, 2, 3]  # no 4 because it have been captured by neutral High priority
UnProductive: [7, 6]  # no 5 and 2 because it have been captured before
Neutral: [4, 5]  # no 2 because it is caputed before

Estou começando com o PostgreSQL e aposto que há uma resposta simples para o meu qs.

Eu tenho uma visão como:

 county - candidate - votes
Abbeville - John Kasich - 157
Abbeville - Ben Carson - 305
Abbeville - Ted Cruz - 876
Abbeville - Jeb Bush - 236
Abbeville - Hillary Clinton - 1501
Abbeville - Marco Rubio - 740
Abbeville - Bernie Sanders - 312
Abbeville - Donald Trump - 1353
Abbot - Hillary Clinton - 0
Abbot - Bernie Sanders - 1
Abington - Ben Carson - 53
Abington - John Kasich - 299
Abington - Bernie Sanders - 1352

E eu gostaria de escrever uma consulta que retorna quem tem mais votos por município como:

    county - candidate - votes
    Abbeville - Hillary Clinton - 1501
    Abbot - Bernie Sanders - 1 
    Abington - Bernie Sanders - 1352 

Eu tentei esta consulta, mas não consigo retornar o candidato certo:

select
distinct on (county) county , candidate ,max(votes) over(partition by county)
from primary_results_csv prc 
group by county, candidate, votes
order by county

Obrigado antecipadamente pela ajuda.

Eu tenho uma tabela de atividades que tem uma referência de chave estrangeira para uma tabela de usuários. Um usuário pode ter muitas atividades e eu tenho um trabalho que preencherá suas atividades diariamente. Depois que esse trabalho for executado, quero executar uma consulta SQL que excluirá os registros mais antigos por usuário e manterá os 10 mais recentes. Este é o esquema:

usuários

id name
1  Jimmy
2  Johnny

user_activities

id user_id activity date
1  1       foo      2020-08-07 00:00:00
2  1       bar      2020-08-08 00:00:00
3  1       baz      2020-08-09 00:00:00
4  2       foo      2020-08-07 00:00:00
5  2       bar      2020-08-08 00:00:00
6  2       baz      2020-08-09 00:00:00

Como posso escrever uma consulta para truncar registros até os 10 últimos para cada usuário?

Digamos que, para simplificar, eu tenha a seguinte tabela:

id      amount     p_id     date
------------------------------------------------
1       5          1        2020-01-01T01:00:00
2       10         1        2020-01-01T01:10:00 
3       15         2        2020-01-01T01:20:00 
4       10         3        2020-01-01T03:30:00 
5       10         4        2020-01-01T03:50:00 
6       20         1        2020-01-01T03:40:00

Aqui está um exemplo de resposta que eu quero:

{
 "2020-01-01T01:00:00": 25, -- this is from adding records with ids: 2 and 3
 "2020-01-01T03:00:00": 55  -- this is from adding records with ids: 3,4,5 and 6
}

Eu quero obter o total ( sum(amount)) de todos os exclusivos p_idagrupados por hora.

A linha escolhida por p_idé aquela com o último date. Por exemplo, o primeiro valor na resposta acima não inclui id 1porque o registro com id 2tem o mesmo p_ide o datedessa linha é posterior.

A única coisa complicada é que eu quero incluir a soma de todos os amountpor p_idse eles dateforem antes da hora apresentada. Por exemplo, no segundo valor da resposta (com a chave "2020-01-01T03:00:00"), embora id 3tenha um carimbo de data/hora em uma hora diferente, é o mais recente para isso p_id 2e, portanto, é incluído na soma de "2020-01-01T03:00:00". Mas a linha com id 6substituições id 2com o mesmo p_id 1.

Em outras palavras: sempre pegue o último amountpara cada p_idaté o momento e calcule a soma para cada hora distinta encontrada na tabela.