2023年3月21日 00:17:18go评论74阅读模式

英文:

How to count incident per group and entity

问题

I have the following SQL structure and data:

incident_1month

id	new_group	date_c
1	1	'2023-02-01 07:16:00'
2	3	'2023-02-01 14:48:00'
3	3	'2023-02-02 18:05:00'
4	4	'2023-02-06 11:31:00'
5	4	'2023-02-08 07:11:00'
6	4	'2023-02-14 09:39:00'

incident_2month

id	new_group	date_c
1	1	'2023-02-01 07:16:00'
2	3	'2023-02-01 14:48:00'
3	3	'2023-02-02 18:05:00'
4	4	'2023-02-06 11:31:00'
5	4	'2023-02-08 07:11:00'
6	4	'2023-02-14 09:39:00'
7	4	'2023-01-14 09:39:00'
8	1	'2023-01-14 09:39:00'
9	2	'2023-01-14 09:39:00'
10	2	'2023-01-14 09:39:00'

persongroup

id	entity_c	name
1	1	'GRP1'
2	2	'GRP2'
3	3	'GRP3'
4	4	'GRP4'
5	5	'GRP5'
6	6	'GRP6'

entity

id	displaylabel
1	'entite1'
2	'entite2'
3	'entite3'
4	'entite4'
5	'entite5'
6	'entite6

Schema

Context

I'm trying to count the number of incidents that occur in the incident_1month and incident_1month tables per group and entity.

I've come up with the following query:

SELECT 
    "persongroup"."name" AS "Groupe", 
    "entity"."displaylabel" AS "Entity",
    COUNT(CASE WHEN "persongroup"."id" IN (SELECT new_group FROM incident_1month) THEN 1 ELSE NULL END) AS "Number of incident 1 month",
    COUNT(CASE WHEN "persongroup"."id" IN (SELECT new_group FROM incident_2month) THEN 1 ELSE NULL END) AS "Number of incident 2 month"
FROM 
    "persongroup" 
LEFT JOIN 
    "entity" ON "persongroup"."entity_c" = "entity"."id" 
GROUP BY "persongroup"."name", "entity"."displaylabel"
ORDER BY "persongroup"."name"

Output:

Groupe	Entity	Number of incident 1 month	Number of incident 2 month
GRP1	entite1	1	1
GRP2	entite2	0	1
GRP3	entite3	1	1
GRP4	entite4	1	1
GRP5	entite5	0	0
GRP6	entite6	0	0

Expected output:

Groupe	Entity	Number of incident 1 month	Number of incident 2 month
GRP1	entite1	1	2
GRP2	entite2	0	1
GRP3	entite3	2	3
GRP4	entite4	3	4
GRP5	entite5	0	0
GRP6	entite6	0	0

I don't understand why my count can't be more than 1.

Demo: https://www.db-fiddle.com/f/24ehsi3qa7fcvVFEcYd323/3

英文:

I have the following SQL structure and data :

incident_1month

id	new_group	date_c
1	1	'2023-02-01 07:16:00'
2	3	'2023-02-01 14:48:00'
3	3	'2023-02-02 18:05:00'
4	4	'2023-02-06 11:31:00'
5	4	'2023-02-08 07:11:00'
6	4	'2023-02-14 09:39:00'

incident_2month

id	new_group	date_c
1	1	'2023-02-01 07:16:00'
2	3	'2023-02-01 14:48:00'
3	3	'2023-02-02 18:05:00'
4	4	'2023-02-06 11:31:00'
5	4	'2023-02-08 07:11:00'
6	4	'2023-02-14 09:39:00'
7	4	'2023-01-14 09:39:00'
8	1	'2023-01-14 09:39:00'
9	2	'2023-01-14 09:39:00'
10	2	'2023-01-14 09:39:00'

persongroup

id	entity_c	name
1	1	'GRP1'
2	2	'GRP2'
3	3	'GRP3'
4	4	'GRP4'
5	5	'GRP5'
6	6	'GRP6'

entity

id	displaylabel
1	'entite1'
2	'entite2'
3	'entite3'
4	'entite4'
5	'entite5'
6	'entite6

Schema

Context

I'm trying to count the number of incident which occur on table incident_1month and incident_1month per group and entity.
I've come up to the following query :

SELECT 
    &quot;persongroup&quot;.&quot;name&quot; AS &quot;Groupe&quot;, 
    &quot;entity&quot;.&quot;displaylabel&quot; AS &quot;Entity&quot;,
    COUNT(CASE WHEN &quot;persongroup&quot;.&quot;id&quot; IN (SELECT new_group FROM incident_1month) THEN 1 ELSE NULL END) AS &quot;Number of incident 1 month&quot;,
    COUNT(CASE WHEN &quot;persongroup&quot;.&quot;id&quot; IN (SELECT new_group FROM incident_2month) THEN 1 ELSE NULL END) AS &quot;Number of incident 2 month&quot;
FROM 
    &quot;persongroup&quot; 
LEFT JOIN 
    &quot;entity&quot; ON &quot;persongroup&quot;.&quot;entity_c&quot; = &quot;entity&quot;.&quot;id&quot; 
GROUP BY &quot;persongroup&quot;.&quot;name&quot;, &quot;entity&quot;.&quot;displaylabel&quot;
ORDER BY &quot;persongroup&quot;.&quot;name&quot;

Output :

Groupe	Entity	Number of incident 1 month	Number of incident 2 month
GRP1	entite1	1	1
GRP2	entite2	0	1
GRP3	entite3	1	1
GRP4	entite4	1	1
GRP5	entite5	0	0
GRP6	entite6	0	0

Expected output :

Groupe	Entity	Number of incident 1 month	Number of incident 2 month
GRP1	entite1	1	2
GRP2	entite2	0	1
GRP3	entite3	2	3
GRP4	entite4	3	4
GRP5	entite5	0	0
GRP6	entite6	0	0

I don't understand why, my count can't be more than 1.

Demo : https://www.db-fiddle.com/f/24ehsi3qa7fcvVFEcYd323/3

答案1

得分: 2

Here are the translated code sections without any additional content:

Original:

if you use correlated subqueries with out the grouping you get your wanted result

you count actually doesn't count the occurrences of the id, it only counts if it is there in the tables incident_2month and incident_1month

Translation:

如果您在不进行分组的情况下使用相关子查询，您将获得所需的结果

实际上，您的计数并不计算 id 的出现次数，它只计算它是否存在于 incident_2month 和 incident_1month 表中

Original:

SELECT 
    "persongroup"."name" AS "Groupe", 
    "entity"."displaylabel" AS "Entity",
    (SELECT COUNT(new_group) FROM incident_1month i WHERE i.new_group = "persongroup"."id")  AS "Number of incident 1 month",
    (SELECT COUNT(new_group) FROM incident_2month i WHERE i.new_group = "persongroup"."id") AS "Number of incident 2 month"
FROM 
    "persongroup" 
LEFT JOIN 
    "entity" ON "persongroup"."entity_c" = "entity"."id" 
ORDER BY "persongroup"."name"

Translation:

SELECT 
    "persongroup"."name" AS "Groupe", 
    "entity"."displaylabel" AS "Entity",
    (SELECT COUNT(new_group) FROM incident_1month i WHERE i.new_group = "persongroup"."id")  AS "Number of incident 1 month",
    (SELECT COUNT(new_group) FROM incident_2month i WHERE i.new_group = "persongroup"."id") AS "Number of incident 2 month"
FROM 
    "persongroup" 
LEFT JOIN 
    "entity" ON "persongroup"."entity_c" = "entity"."id" 
ORDER BY "persongroup"."name"

Original:

You could join the already counted number

SELECT 
    "persongroup"."name" AS "Groupe", 
    "entity"."displaylabel" AS "Entity",   
    COALESCE(m1.num,0) AS "Number of incident 1 month",
    COALESCE(m2.num,0) AS "Number of incident 2 month"
FROM 
    "persongroup" 
LEFT JOIN 
    "entity" ON "persongroup"."entity_c" = "entity"."id" 
LEFT JOIN ( SELECT COUNT(*) num , new_group FROM incident_1month GROUP BY new_group) m1 ON m1.new_group = "persongroup"."id"
LEFT JOIN ( SELECT COUNT(*) num , new_group FROM incident_2month GROUP BY new_group) m2 ON m2.new_group = "persongroup"."id"
--GROUP BY "persongroup"."name", "entity"."displaylabel"
ORDER BY "persongroup"."name"

Translation:

您可以连接已经计算的数字

SELECT 
    "persongroup"."name" AS "Groupe", 
    "entity"."displaylabel" AS "Entity",   
    COALESCE(m1.num,0) AS "Number of incident 1 month",
    COALESCE(m2.num,0) AS "Number of incident 2 month"
FROM 
    "persongroup" 
LEFT JOIN 
    "entity" ON "persongroup"."entity_c" = "entity"."id" 
LEFT JOIN ( SELECT COUNT(*) num , new_group FROM incident_1month GROUP BY new_group) m1 ON m1.new_group = "persongroup"."id"
LEFT JOIN ( SELECT COUNT(*) num , new_group FROM incident_2month GROUP BY new_group) m2 ON m2.new_group = "persongroup"."id"
--GROUP BY "persongroup"."name", "entity"."displaylabel"
ORDER BY "persongroup"."name"

I hope this helps!

英文:

if you use correlated subqueries with out the grouping you get your wanted result

you count actually doesn't count the occurances of the id, it only count if it is there in the tables incident_2month and incident_1month

SELECT 
    &quot;persongroup&quot;.&quot;name&quot; AS &quot;Groupe&quot;, 
    &quot;entity&quot;.&quot;displaylabel&quot; AS &quot;Entity&quot;,
    (SELECT COUNT(new_group) FROM incident_1month i WHERE i.new_group = &quot;persongroup&quot;.&quot;id&quot;)  AS &quot;Number of incident 1 month&quot;,
    (SELECT COUNT(new_group) FROM incident_2month i WHERE i.new_group = &quot;persongroup&quot;.&quot;id&quot;) AS &quot;Number of incident 2 month&quot;
FROM 
    &quot;persongroup&quot; 
LEFT JOIN 
    &quot;entity&quot; ON &quot;persongroup&quot;.&quot;entity_c&quot; = &quot;entity&quot;.&quot;id&quot; 
ORDER BY &quot;persongroup&quot;.&quot;name&quot;

Groupe	Entity	Number of incident 1 month	Number of incident 2 month
GRP1	entite1	1	2
GRP2	entite2	0	1
GRP3	entite3	2	3
GRP4	entite4	3	4
GRP5	entite5	0	0
GRP6	entite6	0	0

fiddle

You could join the already counted number

SELECT 
    &quot;persongroup&quot;.&quot;name&quot; AS &quot;Groupe&quot;, 
    &quot;entity&quot;.&quot;displaylabel&quot; AS &quot;Entity&quot;,   
    COALESCE(m1.num,0) AS &quot;Number of incident 1 month&quot;,
    COALESCE(m2.num,0) AS &quot;Number of incident 2 month&quot;
FROM 
    &quot;persongroup&quot; 
LEFT JOIN 
    &quot;entity&quot; ON &quot;persongroup&quot;.&quot;entity_c&quot; = &quot;entity&quot;.&quot;id&quot; 
LEFT JOIN ( SELECT COUNT(*) num , new_group FROM incident_1month GROUP BY new_group) m1 ON m1.new_group = &quot;persongroup&quot;.&quot;id&quot;
LEFT JOIN ( SELECT COUNT(*) num , new_group FROM incident_2month GROUP BY new_group) m2 ON m2.new_group = &quot;persongroup&quot;.&quot;id&quot;
--GROUP BY &quot;persongroup&quot;.&quot;name&quot;, &quot;entity&quot;.&quot;displaylabel&quot;
ORDER BY &quot;persongroup&quot;.&quot;name&quot;

Groupe	Entity	Number of incident 1 month	Number of incident 2 month
GRP1	entite1	1	2
GRP2	entite2	0	1
GRP3	entite3	2	3
GRP4	entite4	3	4
GRP5	entite5	0	0
GRP6	entite6	0	0

fiddle

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

如何计算每个组和实体的事件数。

问题

答案1

将结果分成五组，使用虚拟数据。

修改后的FNV-1哈希算法的Go语言实现

SQL CH 计算选择的字节数大小

JOIN tables, only selecting newest row from second table, and conditional on second table data

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论