如何在SQLHIVE中根据前面值的逻辑为后面的列赋值？

fae0ux8s 于 2023-02-15 发布在 Hive

关注(0)|答案(1)|浏览(315)

我正在尝试找到一种方法，根据前面值的逻辑为下一列赋值。例如，假设我们有一个包含temp列的表。如果temp超过95，则表应显示为"系统关闭"，如果下一个值大于80，则状态列仍显示为"系统关闭"，仅当下一个值小于80时，此状态才会更改。请参阅示例：
| 位置ID|事件时间|温度|系统状态：所需列（有效连续值）|
| - ------|- ------|- ------|- ------|
| 九九九九九九九九九九九九|下午4时18分58秒|八十点三|"系统开启"|
| 九九九九九九九九九九九九|下午4时21分03秒|七十点五|"系统开启"|
| 九九九九九九九九九九九九|下午4时21分42秒|九十六点二|"系统关闭"|
| 九九九九九九九九九九九九|下午4时25分04秒|九十五点三|"系统关闭"|
| 九九九九九九九九九九九九|下午4时25分40秒|八十二点一|"系统关闭"|
| 九九九九九九九九九九九九|下午4时25分45秒|七十九岁|"系统开启"|
| 九九九九九九九九九九九九|下午4时26分14秒|95分|"系统关闭"|
| 九九九九九九九九九九九九|下午4时26分23秒|七十二岁|"系统开启"|
下面是我尝试过的SQL，但它并不像我希望的那样工作。

> ```
> WITH input_data AS (
  SELECT *,
         CASE
           WHEN Temp >= 95 THEN 'System Off'
           ELSE 'System On'
         END AS valid_current_value
  FROM table
),
consecutive_values AS (
  SELECT *,
         SUM(CASE
               WHEN valid_current_value = 'System Off' THEN 1
               ELSE 0
             END)
           OVER (ORDER BY Event_Time
                 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS consecutive_falses
  FROM input_data
),
consecutive_values_above_80 AS (
  SELECT *,
         SUM(CASE
               WHEN Temp >= 80 THEN 1
               ELSE 0
             END)
           OVER (ORDER BY Event_Time
                 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS consecutive_values_above_80
  FROM consecutive_values
)
SELECT *,
       CASE
         WHEN consecutive_falses >= 2 OR consecutive_values_above_80 >= 2 THEN 'System Off'
         ELSE 'System On'
       END AS valid_consecutive_values
FROM consecutive_values_above_80
> ```

逻辑：如果当前单元格为95，则"系统关闭"。如果下一个值大于80，则"系统关闭"。此逻辑将继续，直到在下一行中找到小于80的值。一旦我们看到小于80的值，则状态更改为"系统打开"。请注意，只有在前面的值为95且后面的值大于80时，此逻辑才起作用。
DESIRED OUTPUT EXAMPLE

Hive

来源：https://stackoverflow.com/questions/75444003/how-to-assign-a-value-to-the-next-following-column-based-on-logic-of-preceding-v

1条答案

按热度按时间

dtcbnfnu1#

这是一个间隙和孤岛问题，您可以尝试以下方法来解决它，查询注解中的解释：

with gaps_islands as
(
  select *,
   sum(case when temp >= 95 then 1 else 0 end) over 
  (partition by LocationID order by EventTime) grp
  /*This is to create groups whenever a temp value goes above 95 */
  from tbl_name
)
select LocationID, EventTime, Temp,
  case 
    when 
      max(temp) over (partition by LocationID, grp) >=95 -- Check that there is a temp value >= 95 within the group 
      and (temp >=80) -- The current temp >=80
      and min(temp) over (partition by LocationID, grp order by EventTime) >=80 -- Check that the last min temp value is >= 80 
     then 'System Off' else 'System On' 
  end as SystemStatus
from gaps_islands
order by EventTime

Check this demo（MySQL上）基于发布的图像中提供的示例数据。

赞(0）回复(0）举报 2023-02-15

我来回答

如何在SQLHIVE中根据前面值的逻辑为后面的列赋值？

1条答案

相关问题

热门标签

最新问答