使用SQL/BigQuery在移動視窗框架中查找第二個最大值

我需要計算過去 120 分鐘幀的移動視窗中的第二個最大值，就像我計算過去 120 分鐘移動幀中在 device_id 上磁區的最大值一樣。

MAX（溫度）OVER（PARTITION BY device_type ORDER BY event_ts_seconds RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW）AS Max_Temp_in_120frame

同樣，我想計算第二個最高溫度。

我已經嘗試了以下 2 個版本的 NTH_VALUE，但它沒有按預期作業。

NTH_VALUE(Temperature,2) OVER(PARTITION BY device_id ORDER BY event_ts_seconds RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW) AS SecondMax_Temp_in_120frame

NTH_VALUE(Temperature,2) OVER(PARTITION BY device_id ORDER BY Temperature DESC RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW) AS SecondMax_Temp_in_120frame

對此的幫助將不勝感激

uj5u.com熱心網友回復：

考慮以下方法

SELECT * EXCEPT(Array_Temp_in_120frame, Two_Max_Temp_in_120frame),
  Two_Max_Temp_in_120frame[SAFE_OFFSET(0)] Max_Temp_in_120frame, 
  Two_Max_Temp_in_120frame[SAFE_OFFSET(1)] SecondMax_Temp_in_120frame 
FROM (
  SELECT *, ARRAY(
      SELECT Temperature FROM t.Array_Temp_in_120frame Temperature
      ORDER BY Temperature DESC LIMIT 2
    ) Two_Max_Temp_in_120frame
  FROM (
    SELECT *, ARRAY_AGG(Temperature) OVER(
        PARTITION BY device_type ORDER BY event_ts_seconds 
        RANGE BETWEEN 7200 PRECEDING AND CURRENT ROW
      ) AS Array_Temp_in_120frame
    FROM your_table
  ) t
)

uj5u.com熱心網友回復：

簡答

你可以這樣做：

WITH sample AS (
  SELECT 
    ts,
    ROUND(10*RAND(), 1) as value
  FROM UNNEST (GENERATE_ARRAY(1, 10)) ts
)
, sample_with_moving_array AS (
    SELECT
        ts,
        value,
        ARRAY_AGG(value) OVER(ORDER BY ts RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_array,
    FROM sample
)
, sample_with_sorted_moving_array AS (
    SELECT
        ts,
        value,
        moving_array,
        ARRAY(SELECT value FROM UNNEST(moving_array) as value ORDER BY value DESC) as sorted_moving_array
    FROM sample_with_moving_array
)
SELECT
    ts,
    value,
    sorted_moving_array[SAFE_ORDINAL(1)] AS max_value_in_3_frame_alternate_version,
    sorted_moving_array[SAFE_ORDINAL(2)] AS second_max_value_in_3_frame,
FROM sample_with_sorted_moving_array

解釋

首先，NTH_VALUE行不通，因為它是視窗函式的泛化FIRST_VALUE，而不是MAX。

在 BigQuery 中，您可以分三步計算第二個最大值。首先，用于ARRAY_AGG計算值的移動陣列：

ARRAY_AGG(value) OVER(ORDER BY ts RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_array

這會給你這樣的東西：

一旦你有了它，使用UNNEST這樣的方式對陣列進行排序：

ARRAY(SELECT value FROM UNNEST(moving_array) as value ORDER BY value DESC) as sorted_moving_array

這會給你這樣的東西（我沒有顯示排名，但你可以看到每個陣列中的值已經排序）：

最后，你可以得到這樣的第二個元素sorted_moving_array：

sorted_moving_array[SAFE_ORDINAL(2)] second_max_value_in_3_frame

ORDINAL意味著您認為陣列的索引從 1 而不是 0 開始。我發現使用ORDINAL(2)而不是獲取第二個元素更具可讀性OFFSET(1)。SAFE_意味著您希望它NULL在索引超出陣列范圍時回傳 a ，而不是拋出錯誤。

最終查詢如下所示：

WITH sample AS (
  SELECT 
    ts,
    ROUND(10*RAND(), 1) as value
  FROM UNNEST (GENERATE_ARRAY(1, 10)) ts
)
, sample_with_moving_array AS (
    SELECT
        ts,
        value,
        ARRAY_AGG(value) OVER(ORDER BY ts RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_array,
    FROM sample
)
, sample_with_sorted_moving_array AS (
    SELECT
        ts,
        value,
        moving_array,
        ARRAY(SELECT value FROM UNNEST(moving_array) as value ORDER BY value DESC) as sorted_moving_array
    FROM sample_with_moving_array
)
SELECT
    ts,
    value,
    MAX(value) OVER(ORDER BY ts RANGE BETWEEN 3 PRECEDING AND CURRENT ROW) AS max_value_in_3_frame,
    sorted_moving_array[SAFE_ORDINAL(1)] AS max_value_in_3_frame_alternate_version,
    sorted_moving_array[SAFE_ORDINAL(2)] AS second_max_value_in_3_frame,
    CONCAT("[", ARRAY_TO_STRING(ARRAY(SELECT CAST(value as STRING) FROM UNNEST(moving_array) value), ", "), "]") AS moving_array_as_string,
    CONCAT("[", ARRAY_TO_STRING(ARRAY(SELECT CAST(value as STRING) FROM UNNEST(sorted_moving_array) value), ", "), "]") AS sorted_moving_array_as_string
FROM sample_with_sorted_moving_array

回傳：

作為控制措施，我還使用舊方法和新方法計算了最大值，以檢查兩者的結果是否相同。

總而言之，您很幸運能夠使用 BigQuery，因為我認為其他 SQL 引擎不支持此查詢中使用的所有功能，至少不支持如此優雅的語法。

標籤：sql 谷歌大查询

上一篇：如何將一張表拆分為具有不同列的多個表（PostgreSQL）？

下一篇：SQL：一元表的子查詢