问题[pivot](dba)

我不知道如何写主题标题...

我有一个数据类似于下面的表格，其中每一行都存储一个与警报相关的事件。例如，事件可能包括：

• 警报激活
• 警报清除/不活动
• 警报由操作员确认

每组事件都与一个eventid.

我想将每组相同的所有事件合并eventid到一行中，使用eventtime作为每个警报事件/操作的值：活动、清除和确认。

源数据：

预期收益：

例如

我怎样才能做到这一点？

我很难理解如何从我正在使用的数据库中正确转置该表。该表是由几年前设计数据库的人创建的，但我设法使用pg_dump.

这是带有示例条目的表：

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]

列中的问题 idquestion_id代表以下内容：

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

响应可以是文本（错误的用户响应），但主要是 a1或 a 0（我感兴趣）。

所以我想把这个表转置成一个新表survey，这样返回的查询结果对于每一列都会有对应的响应码值作为列名（32 -> foot; 33 -> bike; 35 -> car.等）

我对所有这些回复都不感兴趣，但 5 : foot, bike(for bike), bus, car, and metro.

因为我在仅检索 5 个感兴趣的响应时遇到了很大的麻烦，所以我开始检索所有这些值以查看我是否正确地做事。事实证明我做错了。

这是我的尝试：

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));

这使：

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

请注意，这不正确，因为列car应该是1.

此外，我并不是对所有的价值观都不感兴趣。相反，只希望是感兴趣的值。

预计出局

我希望将我的返回结果限制为以下（正确答案）：

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

注意：此dbfiddle说明了我的尝试。

编辑

就评论而言，已编辑问题以显示完整的预期输出。

专家们，

我有这个数据 -

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

现在，当在 Excel 中如此简单时，我正在尝试并未能在 PSQL 中获得输出->

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

如果您能指导我/给我正确的方向，将不胜感激

我有一行以年龄桶作为列名。我希望他们都在一个列中，无论我最终做什么，他们的计数或总和。我有很多桶，但为了这个例子，我保持简单。请参阅随附的数据图片和所需的结果。

所以这就是我所拥有的......

但我正在尝试找到一种方法来获得这种结果......

所以如果我能在查询部分得到任何帮助......我仍然是 psql 的初学者，所以如果你也能解释一下......谢谢

我无法创建遵循下述架构的数据透视表。例如，使用下表（此处未显示所有记录）：

我想制作下表：

我可以使用下面包含的查询创建下表，但我缺少输出中需要的 Route 字段（如上所示）。

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]

是否可以在 PostgreSQL 中对表进行交叉表，而不必定义元组（列类型）给出的“as”子句？

示例：创建的表“t”将只有 3 列“1”、“2”和“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)

我目前正在使用 Linux 机器和临时构建的 Perl 脚本将我的太阳能逆变器数据上传到 PVOutput。我想添加来自我的能源供应商的仪表数据。我从我的能源供应商处下载了一个 87,000 行的 CSV 文件，其中包含以下（样本）数据：

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

通过创建一个虚拟列 (MyDate) 并使用以下选择：

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

我已经能够按摩 SQLite 中的数据给我：

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

但是对于上传到 PVOutput，我正在寻找的格式是：

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

如您所见，我的 SQL 技能很弱，任何帮助将不胜感激。

菲尔。

基本上，如果我们有这个：

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

目标是得到一个看起来像这样的字符串：

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

这里的问题是列的数量和名称都是未知的。现在，有一些方法可以使用PIVOT动态 SQL 来做到这一点，但一般来说，如果您只是将动态 SQL 与一堆随机数据一起使用，那么它可能很容易被注入。在 Stack Overflow 和这个网站上，我看到一些问题的答案使用了这种组合，但他们要么没有提到注射，要么声称它对注射免疫，这让我有点不安。

那么......有没有办法在 SQL Server 中没有动态查询的情况下做到这一点？SQL 注入并不总是立竿见影的。有时注入的代码会在表中处于休眠状态，等待在动态 SQL 查询中使用。在不使用动态 SQL 的情况下如何做到这一点- 或者如果做不到这一点，至少有一种真正不易受到注入攻击的方法吗？

针对评论的两点说明：

#1：至于是否PIVOT使用，就我个人而言，我真正想做的就是切换行和列并从中创建一个 JSON 对象。我有一个看起来有点类似于上面的表，至少在一种情况下，它被用来存储 C# 字典中的键和值。但是，在使用时JsonConvert.DeserializeObject，我需要它看起来像这样：

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

不是这样的：

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

出于某些原因，如果可能的话，我希望在 SQL 中进行这种转换。

#2：SQL注入的问题是：当你连接值时，如果其中一个值中有一个whatever'; SELECT * FROM SomeOtherTable; --类型的脚本，即使最初插入时脚本没有运行（因为很好地使用了SQL参数和类似的预防措施），如果它只是盲目地连接到动态 SQL 查询中，它仍然可以在以后运行。

如果不能完全防止这种情况，我将不得不改变主意并在 C# 中处理这个问题。我真的不能假设数据中没有像那样疯狂的东西，所以我不愿意将它连接到正在运行的东西中，除非有一种真正安全的方法（不仅仅是逃避滴答声）这样做。

我有一个 Timescale DB / PostgreSQL 表（DataTable），看起来像这样

我正在尝试找到一个查询，该查询将为不同的子系统/指标组合返回不同的列，如下所示

此外，我想使用 Timescale DB 函数 time_bucket_gapfill() 减少请求的数据点的数量，以在大时间范围内保持较低的响应时间。我还想为不同的子系统/指标组合使用不同的聚合函数。我使用查询的数据在 Grafana 中绘制曲线。实现这一目标最省时的方法是什么？

目前，我对每个指标使用单独的查询，这很快就会变得非常慢。例如，查询可能看起来像这样

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

如果有人能指出我正确的方向，我将不胜感激。

更新：

使用@Charlieface 建议的 FILTER 子句对我不起作用，因为它不会删除其他行，而是将 NULL 放在那里。这是一个包含一些真实数据的示例（之前的示例使用了简化的数据和名称）。

我还注意到，属于一起的指标的时间戳有微小的差异，这会阻止不同的指标位于同一行，但这很容易在将数据输入数据库的单元中进行更改。

更新 2：

时间戳问题已修复，并按照@Charlieface 的建议再次添加了 WHERE 语句，但数据中仍然存在 NULL，这使得 Grafana 绘制数据点之间没有线条。

我希望结果看起来像这样。

更新 3：

我错过了@Charlieface 只按时间分组回答。如果我这样做，那么我会得到我想要的结果。