在SQL中对分类数据应用模式操作 [英] Apply mode operation on categorical data in SQL

问题描述

我希望基于滑动窗口处理BigQuery数据库中的分类日志数据。我要在大小为3或5的窗口上应用模式操作，以便丢弃一次性事件或类别更改。

|SysDT | Power_State | Target |
| -------- | -------- | -------- |
|2021-07-01 09:03:57+00:00| EDC | EDC   |
|2021-07-01 09:08:57+00:00| EDC | EDC   |
|2021-07-01 09:13:57+00:00| DWN | EDC   |
|2021-07-01 09:18:57+00:00| EDC | EDC   |
|2021-07-01 09:23:58+00:00| EDC | EDC   |
|2021-07-01 09:28:59+00:00| DWN | EDC   |
|2021-07-01 09:33:59+00:00| EDC | EDC   |

我尝试使用OVER操作符，它为我提供了所需的滑动窗口，但是接下来我需要一个自定义模式操作符。修改此查询以避免此类模式函数或在BigQuery中编写自定义模式函数有什么想法吗？

SELECT *, MODE(Power_State) 
    OVER(ORDER BY SysDT ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as Target
FROM Master_Data.2021_07
ORDER BY SysDT

任何帮助都非常感谢。谢谢

推荐答案

考虑以下方法

select sysdt, power_state,
  ( select approx_top_count(state, 1)[offset(0)].value
    from unnest(arr) state
  ) as target
from (
  select *, array_agg(power_state) over win arr
  from your_table
  window win as (order by sysdt rows between 1 preceding and 1 following)
)
-- order by sysdt      

如果应用于您问题中的样本数据

with your_table as (
  select '2021-07-01 09:03:57+00:00' sysdt, 'EDC' power_state union all 
  select '2021-07-01 09:08:57+00:00', 'EDC' union all 
  select '2021-07-01 09:13:57+00:00', 'DWN' union all 
  select '2021-07-01 09:18:57+00:00', 'EDC' union all 
  select '2021-07-01 09:23:58+00:00', 'EDC' union all 
  select '2021-07-01 09:28:59+00:00', 'DWN' union all 
  select '2021-07-01 09:33:59+00:00', 'EDC' 
)           

输出为

这篇关于在SQL中对分类数据应用模式操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！