编写 MySQL 查询以获得测试表的预期结果

问题描述：

我正在使用 MySQL 数据库.

I am working with MySQL database.

表格包含以下数据:

id	uaid	属性	价值	时间	风险因素
1	1234	文件路径	存在	16123	无
2	1234	文件路径	不存在	16124	关键
3	1234	文件路径	存在	16125	无

所需的结果应如下所示:

the required result should be like below:

属性	风险因素	UAID	失败值	现值
文件路径	关键	1234	不存在	存在

说明:

我们需要显示具有关键风险因素的数据.

we need to show data which have risk factor critical.

失败值 = 在风险因素至关重要时(最新的)，然后该属性的值表示为失败值

Failed Value = at the time (latest one) when risk factor is critical then value for that attribute represent as failed value

当前值 = 它表示为数据库中该属性的当前值.

Present value = it is represented as current value for that attribute in database.

我尝试了两个 sql 查询的解决方案.一种用于获取风险因子等于关键的行.第二个用于获取每个唯一属性的当前值.然后对来自两个查询的数据进行一些格式化.

i have tried with the solution of two sql queries. one for taking getting rows which have risk factor equal to critical. and the second one for getting current value of each unique attribuite. and then some formatting of data from the both queries.

我正在寻找可以根据要求消除数据格式化的额外开销的解决方案.

I am looking for solution which removes the extra overhead of data formatting according to requirement.

架构表(id,uaid,attribute,value,time,risk_factor)

Schema table(id,uaid,attribute,value,time,risk_factor)

答

假设一个组是一个具有相同 (uaid, attribute) 值的行集:

Assuming that a group is a rowset with the same (uaid, attribute) values:

WITH cte AS (
SELECT DISTINCT
       attribute,
       'CRITICAL' `Risk Factor`,
       uaid,
       FIRST_VALUE(value) OVER (PARTITION BY uaid, attribute 
                                ORDER BY `risk factor` = 'CRITICAL' DESC, `time` DESC) `Failed Value`,
       FIRST_VALUE(value) OVER (PARTITION BY uaid, attribute 
                                ORDER BY `time` DESC) `Present Value`,
       SUM(`risk factor` = 'CRITICAL') OVER (PARTITION BY uaid, attribute) is_critical
FROM source_table
)
SELECT Attribute, `Risk Factor`, UAID, `Failed Value`, `Present Value`
FROM cte
WHERE is_critical;

小提琴

编写 MySQL 查询以获得测试表的预期结果

相关推荐