Perguntas[pivot](dba)

Não sei como escrever o título do tópico...

Tenho uma tabela com dados semelhantes a abaixo, onde cada linha armazena um evento associado aos alarmes. Por exemplo, os eventos podem incluir:

• alarme fica ativo
• alarme apaga/inativo
• alarme é reconhecido por um operador

Cada grupo de eventos está associado a um único arquivo eventid.

Eu quero combinar todos os eventos para cada grupo do mesmo eventidem uma única linha, usando o eventtimecomo o valor para cada um dos eventos/ações de alarme: ativo, limpo e confirmado.

dados de origem:

retorno esperado:

Por exemplo

Como posso fazer isso?

Estou tendo dificuldade em entender como transpor corretamente esta tabela de um banco de dados que estou usando. Esta tabela foi criada por alguém que projetou o banco de dados anos atrás, mas consegui recuperar a instrução create da tabela usando pg_dump.

Aqui está a tabela com uma entrada de exemplo:

CREATE TABLE response (
    session_id integer NOT NULL,
    seconds integer NOT NULL,
    question_id integer NOT NULL,
    response character varying(500),
    file bytea
);

INSERT INTO response(session_id, seconds, question_id, response, file)
VALUES (758,1459505869,31,'0',''),  (758,1459505869,32,'0',''), 
(758,1459505869,33,'0',''), (758,1459505869,34,'0',''), 
(758,1459505869,35,'1',''), (758,1459505869,36,'0',''), 
(758,1459505869,37,'0',''), (758,1459505869,38,'0',''), 
(758,1459506973,38,'0',''), (758,1459506973,37,'0',''), 
(758,1459506973,36,'0',''),(758,1459506973,35,'1',''),  
(758,1459506973,34,'0',''),(758,1459506973,33,'0',''),  
(758,1459506973,32,'0',''),(758,1459506973,31,'0',''),
(758,1459508676,31,'0',''),(758,1459508676,32,'0',''),  
(758,1459508676,33,'0',''),(758,1459508676,34,'0',''),  
(758,1459508676,35,'1',''),(758,1459508676,36,'0',''),  
(758,1459508676,37,'0', ''),    (758,1459508676,38,'0', '');

SELECT * FROM response LIMIT 5;
session_id  seconds   question_id   response    file
   758     1459505869     31           0         [null]
   758     1459505869     32           0         [null]
   758     1459505869     33           0         [null]
   758     1459505869     34           0         [null]
   758     1459505869     35           1         [null]

O ID da pergunta na question_idcoluna significa o seguinte:

30  -- not_foot_count 
31  -- not_moving
32  -- foot
33  -- bicycle
34  -- motorcycle
35  -- car
36  -- bus
37  -- metro
38  -- other
39  -- train

A resposta pode ser texto (resposta errada do usuário), mas principalmente a 1ou a 0(que são do meu interesse).

Então eu quero transpor esta tabela para uma nova tabela survey, para que o resultado da consulta retornada tenha para cada coluna, o valor do código de resposta correspondente como o nome da coluna ( 32 -> foot; 33 -> bike; 35 -> car. etc)

Não estou interessado em todas essas respostas, mas 5 : foot, bike(para bicicleta), bus, car, e metro.

Como tive grande dificuldade em recuperar apenas as 5 respostas de interesse, comecei recuperando todos esses valores para ver se estou fazendo as coisas corretamente. Acontece que estou fazendo as coisas erradas.

Segue minha tentativa:

CREATE TABLE survey
AS
SELECT aresult.session_id,
   aresult.not_foot_count,
    aresult.not_moving,
    aresult.foot,
    aresult.bike,
    aresult.motor,
    aresult.car,
    aresult.bus,
    aresult.metro,
    aresult.train,
    aresult.other
   FROM crosstab('select session_id, question_id, response 
                from  response
                order by session_id,question_id'::text) 
  aresult(session_id integer, not_foot_count character varying(500), 
  not_moving character varying(500), foot character varying(500), 
  bike character varying(500), motor character varying(500), 
  car character varying(500), bus character varying(500), 
  metro character varying(500), train character varying(500), 
  other character varying(500));

Que dá:

SELECT * FROM survey;
session_id seconds not_foot_count not_moving foot bike motor car bus metro train other
758       1459505869       0          0        0   0    0     0   0   0      0     0
758       1459506973       0          0        0   0    0     0   0   0      0     0
758       1459508676       0          0        0   0    0     0   0   0      0     0

Observe que isso não está correto como a coluna cardeveria estar 1.

Além disso, não estou interessado em todos os valores. Em vez disso, gostaria que apenas os valores de interesse fossem.

Esperado para fora

Espero limitar meu resultado de retorno ao seguinte (com a resposta correta):

session_id  seconds    foot bike car bus metro 
    758   1459505869    0     0   1   0    0
    758   1459506973    0     0   1   0    0
    758   1459508676    0     0   1   0    0

Nota: Minha tentativa é ilustrada neste dbfiddle .

EDITAR

Quanto aos comentários, pergunta editada para mostrar a saída esperada completa.

Especialistas,

Eu tenho esses dados -

ID       |  buckets      | SUM (USD)|
--------- --------------- ---------
1        | 10 - 20       | 500      |
--------- --------------- ---------
1        | 30 - 40       | 100      |
--------- --------------- ---------
2        | 30 - 40       | 300      |
--------- --------------- ---------

Agora a saída que estou tentando e não conseguindo no PSQL quando é tão fácil no Excel ->

ID       |. 10-20        | 30-40    | Total_USD |
--------- --------------- ---------  -----------
1        |   500         |  100     |    600    |
--------- --------------- ---------  -----------
2        |               |  300     |    300    | 
--------- --------------- ---------  -----------

Agradeceria se você pudesse me orientar/enviar a direção certa

Eu tenho uma única linha com intervalos de idade como nomes de coluna. Eu quero todos eles em uma coluna com sua contagem ou soma, o que quer que eu acabe fazendo. Eu tenho alguns baldes, mas por causa deste exemplo eu o mantive simples. Por favor, veja a imagem em anexo dos dados e o resultado que é necessário.

então é isso que eu tenho...

mas estou tentando encontrar uma maneira de obter esse tipo de resultado ......

então se eu puder obter alguma ajuda na parte da consulta.... ainda sou iniciante no psql, então se você puder explicar também... obrigado

Estou tendo problemas para criar uma tabela dinâmica que segue o esquema descrito abaixo. Por exemplo, com a tabela a seguir (nem todos os registros são mostrados aqui):

Gostaria de produzir a seguinte tabela:

Consigo criar a tabela a seguir com a consulta incluída abaixo, mas estou faltando o campo Route que preciso como parte da minha saída (como mostrado acima).

SELECT [Bus_Fare_Payment_Method] "Bus Fare Payment Method", [Under $10k] 'Under $10k', [$10K to $14K] '$10K to $14K',[$15k to $19k] '$15k to $19k', [$20k to $24k] '$20k to $24k', [$25k to $29k] '$25k to $29k', [$30k to $39k] '$30k to $39k', [$40k to $49k] '$40k to $49k', [$50k to $59k] '$50k to $59k', [$60k or more] '$60k or more'
FROM   
(SELECT [Route], [Total_Annual_Household_Income], [Bus_Fare_Payment_Method]  
FROM [BCT_TDP_SURVEY_2018] where [Bus_Fare_Payment_Method] != '' ) p  
PIVOT  
(  
COUNT ([Route])  
FOR [Total_Annual_Household_Income] IN  
( [Under $10k], [$10K to $14K],[$15k to $19k], [$20k to $24k], [$25k to $29k], [$30k to $39k], [$40k to $49k], [$50k to $59k], [$60k or more] )  
) AS pvt  
ORDER BY pvt.[Bus_Fare_Payment_Method]

É possível fazer crosstab em uma tabela no PostgreSQL sem ter que definir uma cláusula "as" fornecida por uma tupla (tipo de coluna)?

Exemplo: A tabela criada "t" teria apenas 3 colunas '1', '2' e '3'.

CREATE TABLE IF NOT EXISTS t (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

INSERT INTO t (id_, value1) VALUES
(1, 10),
(1, 20),
(2, 10),
(2, 20),
(2, 20),
(3, 30),
(3, 20)

Atualmente, estou carregando meus dados do inversor solar para o PVOutput usando uma máquina Linux e um script Perl construído do zero. Quero adicionar os dados do medidor do meu fornecedor de energia. Eu tenho um arquivo CSV de 87.000 linhas baixado do meu fornecedor de energia que contém os seguintes dados (amostra):

NMI,Meter Serial Number,Stream,Register Number,Register Description,UOM,ReadingDate,Units,Read Type<br>
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:45,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:45,0.093,  Actual
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:30,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:30,0.085,  Actual
700555,7006444,B,B1,Solar energy,kWh,23-09-21 23:15,0,      Actual
700555,7006444,E,E1,Offpeak,     kWh,23-09-21 23:15,0.085,  Actual

Criando uma coluna virtual (MyDate) e usando o seguinte selecione:

select MyDate, "Register Description", sum(Units) 
from MeterData group by MyDate, "Register Description"

Consegui massagear os dados no SQLite para me fornecer:

20210920|Offpeak|3.821
20210920|Peak|4.954
20210920|Shoulder|5.695
20210920|Solar energy|2.958
20210921|Offpeak|3.831
20210921|Peak|4.41
20210921|Shoulder|4.428
20210921|Solar energy|4.354
20210922|Offpeak|4.355
20210922|Peak|6.572
20210922|Shoulder|1.961
20210922|Solar energy|7.217

Mas para upload para PVOutput, o formato que estou procurando é:

20210919|Offpeak|3.514|Peak|8.453|Shoulder|7.445|Solar energy|2.792
20210920|Offpeak|3.821|Peak|4.954|Shoulder|5.695|Solar energy|2.958
20210921|Offpeak|3.831|Peak|4.410|Shoulder|4.428|Solar energy|4.354
20210922|Offpeak|4.355|Peak|6.572|Shoulder|1.961|Solar energy|7.217

Como você pode ver, minhas habilidades em SQL são fracas e qualquer ajuda seria muito apreciada.

Fil.

Essencialmente, se tivermos isso:

create table #temp ([key] nchar, [value] nvarchar(2));
insert #temp ([key], [value]) values
             ('a',   '1'),
             ('b',   '2'),
             ...
             ('z',   '26')

o objetivo seria obter uma string de volta que se parece com isso:

{"a":"1","b":"2",..."z":"26"}

O problema aqui é que a contagem - e os nomes - das colunas são ambos desconhecidos. Agora, existem maneiras de fazer isso usando PIVOTSQL dinâmico, mas em geral, o SQL dinâmico pode ser propenso a injeção se você o estiver usando apenas com um monte de dados aleatórios. No Stack Overflow e neste site, vejo algumas perguntas com respostas postadas que usam essa combinação, mas elas não mencionam a injeção ou afirmam que é imune à injeção, o que me deixa um pouco desconfortável.

Então... existe alguma maneira de fazer isso sem uma consulta dinâmica no SQL Server? A injeção de SQL nem sempre é imediata; às vezes, o código injetado fica inativo dentro de tabelas, esperando para ser usado em consultas SQL dinâmicas. Como você pode fazer isso sem usar SQL dinâmico - ou, falhando nisso, existe pelo menos realmente uma maneira que não seja vulnerável à injeção?

Duas notas, em resposta aos comentários:

#1: No que diz respeito a PIVOTser usado ou não, tudo o que realmente quero fazer, no meu caso pessoal, é alternar linhas e colunas e criar um objeto JSON a partir disso. Eu tenho uma tabela que se parece um pouco com a acima, que está sendo usada, pelo menos em um caso, para armazenar chaves e valores de um dicionário C#. No entanto, ao usar JsonConvert.DeserializeObject, preciso que fique assim:

{
    "Key1": "Value1",
    "Key2": "Value2",
    ...
}

Assim não:

[{
    "Key": "Key1",
    "Value": "Value1",
}, {
    "Key": "Key2",
    "Value": "Value2"
}, {
    ...
}]

Por alguns motivos, quero que essa transformação seja feita em SQL, se possível.

#2: A preocupação com a injeção de SQL é esta: Quando você concatena valores, se um dos valores tiver um whatever'; SELECT * FROM SomeOtherTable; --tipo de script nele, mesmo que o script não tenha sido executado quando foi inserido originalmente (devido ao bom uso dos parâmetros SQL e precauções como essa), ele ainda pode ser executado mais tarde se for apenas concatenado cegamente em uma consulta SQL dinâmica.

Terei que mudar de ideia e lidar com isso em C#, se isso não puder ser perfeitamente protegido. Eu realmente não posso assumir que os dados não têm algo louco assim lá, então não me sinto confortável em concatená-los em algo que é executado, a menos que haja uma maneira verdadeiramente segura (mais do que apenas escapar de ticks) para fazê-lo.

Eu tenho uma tabela Timescale DB / PostgreSQL (DataTable) que se parece com isso

Estou tentando encontrar uma consulta que retorne colunas diferentes para diferentes combinações de subsistema/métricas como esta

Além disso, gostaria de reduzir a quantidade de pontos de dados solicitados usando a função Timescale DB time_bucket_gapfill() para manter o tempo de resposta baixo para grandes intervalos de tempo. Eu também gostaria de usar diferentes funções de agregação para diferentes combinações de subsistema/métrica. Eu uso os dados consultados para plotar as curvas no Grafana. Qual é a maneira mais eficiente de conseguir isso?

No momento eu uso consultas separadas para cada métrica que rapidamente fica muito lenta. Para o exemplo, as consultas podem ser assim

SELECT
    time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
    max(value) AS "Latitude"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
    subsystem = 'position' AND
    metric = 'lat'
GROUP BY 1,metric,subsystem
ORDER BY time

SELECT
    time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
    min(value) AS "Longitude"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
    subsystem = 'position' AND
    metric = 'lon'
GROUP BY 1,metric,subsystem
ORDER BY time

SELECT
    time_bucket_gapfill('30s',time,start=>'2021-07-19T09:06:26.605Z',finish=>'2021-07-19T20:11:12.340Z') AS "time",
    avg(value) AS "Temperature"
FROM DataTable
WHERE
    time BETWEEN '2021-07-19T09:06:26.605Z' AND '2021-07-19T20:11:12.340Z' AND
    subsystem = 'health' AND
    metric = 'temperature'
GROUP BY 1,metric,subsystem
ORDER BY time

Eu agradeceria muito se alguém pudesse me apontar na direção certa.

Atualizar:

Usar a cláusula FILTER como sugerido por @Charlieface não funciona para mim, porque não remove as outras linhas, mas coloca NULLs lá. Aqui um exemplo com alguns dados reais (o exemplo anterior estava usando dados e nomes simplificados).

Também notei que os timestamps das métricas que pertencem juntas têm pequenas diferenças que impediriam que métricas diferentes estivessem na mesma linha, mas isso é fácil de alterar na unidade que alimenta os dados no banco de dados.

Atualização 2:

O problema de timestamps foi corrigido e a instrução WHERE foi adicionada novamente como sugerido por @Charlieface, mas ainda há NULL nos dados, o que faz o Grafana plotar os pontos de dados sem linhas entre eles.

Gostaria que o resultado ficasse assim.

Atualização 3:

Eu perdi essa resposta do @Charlieface apenas grupos por tempo. Se eu fizer isso, obterei o resultado que eu queria.