Joyce' Blog
0%

sql查询标注进度

发表于

创建视图 url_info_sencond 用以表示第二次分配的url，筛选条件：

image-20210222215647289

查询第二次分配的标注进度：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
`user`.id AS `用户ID`,
`user`.username AS `用户姓名`,
Count(distinct person_url_relation.rel_person_id) AS `已标注患者总数`,
Count(distinct person_trace.trace_id) AS `已标注轨迹总数`,
Count(distinct url_info_second.url_id) AS `已分配的url数`
FROM
`user`
LEFT JOIN url_info_second ON url_info_second.user_id = `user`.id
LEFT JOIN person_url_relation ON person_url_relation.rel_url_id = url_info_second.url_id
LEFT JOIN person_trace ON person_url_relation.rel_person_id = person_trace.person_id
WHERE `user`.id > 14 
GROUP BY
`user`.id

v2:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT * FROM 
(
	SELECT
	`user`.id AS user_id,
	`user`.username AS user_name,
	Count(distinct person_url_relation.rel_person_id) AS totalP,
	Count(distinct person_trace.trace_id) AS totalT,
	Count(distinct url_info_second.url_id) AS totalU
	FROM
	`user`
	LEFT JOIN url_info_second ON url_info_second.user_id = `user`.id
	LEFT JOIN person_url_relation ON person_url_relation.rel_url_id = url_info_second.url_id
	LEFT JOIN person_trace ON person_url_relation.rel_person_id = person_trace.person_id
	WHERE `user`.id > 14 
	GROUP BY
	`user`.id
) AS finshed_t WHERE finshed_t.totalU <> 0

统计第二次未完成标注的url数量：

1
2
3
4
5
6
7
8
9
10
11
12
SELECT `user`.id as '用户ID', IFNULL(k.unfinshed, 0) as '未完成标注的url数'
FROM `user` LEFT JOIN 
(
SELECT
url_info_second.user_id AS user_id,
Count(distinct url_info_second.url_id) AS unfinshed
FROM url_info_second
WHERE url_condition = '待标注'
GROUP BY
url_info_second.user_id
)AS k ON k.user_id = `user`.id
WHERE `user`.id > 14

合并以上两个结果：[Err] 1248 - Every derived table must have its own alias

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT 
user_id1 AS `用户ID`,
user_name AS `用户名`,
totalP AS `已标注患者人数`,
totalT AS `已标注轨迹总数`,
totalU AS `已分配的url数`,
unfinshed AS `未完成标注的url数`
FROM
(
	SELECT * FROM
	(
		SELECT * FROM 
		(
			SELECT
			`user`.id AS user_id1,
			`user`.username AS user_name,
			Count(distinct person_url_relation.rel_person_id) AS totalP,
			Count(distinct person_trace.trace_id) AS totalT,
			Count(distinct url_info_second.url_id) AS totalU
			FROM
			`user`
			LEFT JOIN url_info_second ON url_info_second.user_id = `user`.id
			LEFT JOIN person_url_relation ON person_url_relation.rel_url_id = url_info_second.url_id
			LEFT JOIN person_trace ON person_url_relation.rel_person_id = person_trace.person_id
			WHERE `user`.id > 14 
			GROUP BY
			`user`.id
		) AS finshed_t WHERE finshed_t.totalU <> 0
	)AS finshed
	LEFT JOIN 
	(
		SELECT * FROM
		(
			SELECT `user`.id as user_id2, IFNULL(k.unfinshed, 0) as unfinshed
			FROM `user` LEFT JOIN 
			(
				SELECT
				url_info_second.user_id AS user_id,
				Count(distinct url_info_second.url_id) AS unfinshed
				FROM url_info_second
				WHERE url_condition = '待标注'
				GROUP BY
				url_info_second.user_id
			)AS k ON k.user_id = `user`.id
			WHERE `user`.id > 14 
		) AS unfinished_url
	) ON 
	finished.user_id1 = unfinshed_url.user_id2
) as t

统计失效url：待审核但无关联人员：

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT * FROM
(
	SELECT
	`user`.id AS user_id,
	`user`.username AS user_name,
	Count(distinct url_info_second.url_id) AS totalU
	FROM
	`user`
	LEFT JOIN url_info_second ON url_info_second.user_id = `user`.id
	WHERE `user`.id > 14 
	GROUP BY
	`user`.id
) AS finshed_t WHERE finshed_t.totalU <> 0
1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM 
(
	SELECT
	url_id, user_id,
	Count(distinct person_url_relation.rel_person_id) AS totalP
	FROM url_info_second
	LEFT JOIN person_url_relation ON person_url_relation.rel_url_id = url_info_second.url_id
	LEFT JOIN person_trace ON person_url_relation.rel_person_id = person_trace.person_id
	WHERE url_condition = '待审核'
	GROUP BY
	url_id
) AS u_p_count

创建 view 时，select 语句无法嵌套

存在问题的url数量：

1
2
3
SELECT user_id,COUNT(url_id)
from url_person_count WHERE totalP = 0
GROUP BY user_id