我需要嵌套兩個查詢。查詢 B 的輸出與 machine_id 相關，它需要進入查詢 A 的函式 LAG(utilisation, N)。在下面的查詢 A 89 應該是查詢 b - 1 的結果

查詢 A

SELECT
  time AS "time",
  machine_id,
  utilisation - LAG(utilisation,89) OVER ( PARTITION BY machine_id ORDER BY time) as DataUsed
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = 'network'
ORDER BY time 

查詢 B

SELECT
  machine_id,
  count(utilisation) AS N
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = "network"
GROUP BY machine_id 

我在 Grafana 上使用它，所以時間引數是范圍

這是表硬體的匯出，其中組件被過濾到“網路”

面板標題-資料-2021-11-27 13_12_14.csv

我試過這個，但沒有用

 SELECT
      A.time AS "time",
      A.machine_id,
      A.utilisation - LAG(A.utilisation,B.N - 1) OVER ( PARTITION BY A.machine_id ORDER BY A.time) as DataUsed
    FROM hardware as A
      INNER JOIN
        (
        SELECT
            machine_id,
            count(utilisation) AS N
        FROM hardware
        WHERE
            $__unixEpochFilter(time) AND
            component = "network"
        GROUP BY machine_id 
        ) as B on A.machine_id = B.machine_id
    WHERE
      $__unixEpochFilter(A.time) AND
      component = 'network'
    ORDER BY A.time

我已經生成了 SQL 查詢并且子查詢有效。我只是不知道如何讓 BN 進入 LAG 并替換 89

SELECT
  A.time AS "time",
  A.machine_id,
  B.N,
  A.utilisation - LAG(A.utilisation,89) OVER ( PARTITION BY A.machine_id ORDER BY A.time) as DataUsed
FROM hardware as A
  INNER JOIN
    (
    SELECT
        machine_id,
        count(utilisation) AS N
    FROM hardware
    WHERE
        time >= 1638058073 AND time <= 1638061673 AND
        component = "network"
    GROUP BY machine_id 
    ) as B on A.machine_id = B.machine_id
WHERE
  A.time >= 1638058073 AND A.time <= 1638061673 AND
  component = 'network'
ORDER BY A.time

uj5u.com熱心網友回復：

以下是一些示例，它們通過用第一個（或最低）資料使用量減去資料使用量來計算資料使用量。請注意，我在示例中用“tyd”替換了“time”列，因為這給我的測驗設定帶來了問題。我還注釋掉了這些$__unixEpochFilter(time)行，因為它們不適用于我的測驗設定。在你這邊取消注釋。

使用滯后

以下將為您提供每個 machine_id 的單個總資料使用結果。它基本上使用第一個 WITH 陳述句（“B”）來確定行數 N，然后使用第二個 WITH 陳述句（“resultt”）來計算使用 LAG 和行數 N 使用的資料。 由于 LAG 計算為所有除了最后一個條目，行在底部被過濾為非空。

-- Calculate single total data usage by counting rows and then using lag to
-- subtract the utilisation from the first utilisation row.

WITH B AS (
    SELECT
        machine_id,
        count(utilisation) AS N
    FROM hardware
    WHERE
        -- $__unixEpochFilter(time) AND
        component = "network"
    GROUP BY machine_id
),
resultt as (
    SELECT
        A.machine_id,
        A.utilisation - (LAG(A.utilisation,(B.N - 1)) OVER ( PARTITION BY A.machine_id ORDER BY A.tyd)) as DataUsed
    FROM hardware A
    JOIN B ON B.machine_id = A.machine_id 
    WHERE
        A.component = 'network'
)
SELECT 
    machine_id,
    DataUsed
FROM resultt
WHERE DataUsed IS NOT NULL -- Since the lag calculation produces null for all rows but the last

第一次減去利用率

下面產生與上面相同的結果，但不是通過 LAG 查找第一次利用率，而是查找第一次的利用率值。

-- Calculate single total data used as the difference between the last
-- utilisation and the utilisation at first time.
-- This uses the first time to determine the first utilisation.
-- If utilisation is always increasing, calculation of the first time
-- is unnecessary, see the other script.

WITH first_time AS (
    -- Get the first time for each machine_id
    SELECT
        machine_id,
        min(tyd) AS min_tyd
    FROM hardware
    WHERE
        -- $__unixEpochFilter(time) AND
        component = 'network'
    GROUP BY machine_id 
), first_util AS (
    -- Get the utilisation corresponding to the first time
    SELECT
        h.machine_id,
        h.utilisation
    FROM hardware h
    JOIN first_time f ON f.machine_id = h.machine_id AND f.min_tyd = h.tyd
)
-- For each row the running utilisation can now be calculated as the difference
-- of the row's utilisation - first utilisation.
SELECT 
    h.tyd, 
    h.machine_id,
    h.utilisation,
    fu.utilisation AS first_util, -- Only for troubleshooting
    max(h.utilisation - fu.utilisation) AS DataUsed
FROM hardware h
JOIN first_util fu ON fu.machine_id = h.machine_id
GROUP BY machine_id
ORDER BY h.machine_id, h.tyd, h.utilisation

減去最低利用率

如果利用率值一直在增加，上面的腳本可以簡化，去掉時間計算，簡單地從最后一個利用率中減去第一個利用率。

-- Calculate the single total data used as the difference between the last
-- and the first (lowest) utilisation for each machine_id.
-- This assumes that utilisation always increases as time passes.

WITH first_util AS (
    -- Get the lowest utilisation for each machine_id
    SELECT
        machine_id,
        min(utilisation) AS min_util
    FROM hardware
    WHERE
        -- $__unixEpochFilter(time) AND
        component = 'network'
    GROUP BY machine_id 
)
-- For each row the running utilisation can now be calculated as the difference
-- of the row's utilisation - first utilisation.
SELECT 
    h.tyd, 
    h.machine_id,
    h.utilisation,
    fu.min_util, -- Only for troubleshooting
    max(h.utilisation - fu.min_util) AS DataUsed
FROM hardware h
JOIN first_util fu ON fu.machine_id = h.machine_id
GROUP BY machine_id
ORDER BY h.machine_id, h.tyd, h.utilisation

獎金 - 總計

以上兩個腳本將結果折疊為單個值以獲得總資料使用量。要獲得資料使用的總和，可以簡單地洗掉 max() 和 ORDER BY：

-- Calculate running total of data used minus the lowest utilisation.
-- This assumes that utilisation always increases as time passes.

WITH first_util AS (
    -- Get the first (lowest) utilisation for each machine_id
    SELECT
        machine_id,
        min(utilisation) as min_util
    FROM hardware
    WHERE
        -- $__unixEpochFilter(time) AND
        component = 'network'
    GROUP BY machine_id 
)
-- For each row the running utilisation can now be calculated as the difference
-- of the row's utilisation - first utilisation.
SELECT 
    h.tyd, 
    h.machine_id,
    h.utilisation,
    fu.min_util, -- Only for troubleshooting
    h.utilisation - fu.min_util AS DataUsed
FROM hardware h
JOIN first_util fu ON fu.machine_id = h.machine_id
ORDER BY h.machine_id, h.tyd, h.utilisation

附注。感謝我的妻子幫助我解決了上述 SQL。

uj5u.com熱心網友回復：

上述問題是在我試圖找到一種方法來獲取時間序列中最新值和最舊值之間的差異時提出的。Gideon 讓我意識到最簡單的方法就是獲取最新值和范圍內的最小值。

SELECT
  time AS "time",
  machine_id,
  utilisation - min(utilisation) OVER ( PARTITION BY machine_id ORDER BY time) as DataUsed
FROM hardware
WHERE
  $__unixEpochFilter(time) AND
  component = 'network'
ORDER BY time 

標籤：mysql sql

上一篇：GoogleAppScriptJdbc函式

下一篇：Mysql范圍檢查而不是索引使用