主页 / coding / 问题 / 79529269
Accepted
DB5FP
Asked: 2025-03-24 00:31:55 +0800 CST

如何在 Mariadb 中查询后获取行之间的差异值（delta）

  • 772

你好，这是我的疑问：

MariaDB [PV_Anlage]>

SELECT created_at, verbrauch_ges 
FROM pv_metrics 
group by DATE(created_at) 
ORDER BY ID DESC limit 20;

+---------------------+---------------+
| created_at          | verbrauch_ges |
+---------------------+---------------+
| 2025-03-23 00:00:04 |       5628.79 |
| 2025-03-22 00:00:06 |       5622.54 |
| 2025-03-21 00:00:11 |       5615.44 |
| 2025-03-20 00:00:13 |       5609.49 |
| 2025-03-19 00:00:17 |        5605.4 |
| 2025-03-18 00:00:00 |       5600.45 |
| 2025-03-17 00:00:03 |       5591.36 |
| 2025-03-16 00:00:09 |        5585.2 |
| 2025-03-15 00:00:16 |       5578.96 |
| 2025-03-14 00:00:04 |        5571.1 |
| 2025-03-13 00:00:07 |       5560.34 |
| 2025-03-12 00:00:10 |       5550.54 |
| 2025-03-11 00:00:13 |       5540.12 |
| 2025-03-10 00:00:16 |       5534.81 |
| 2025-03-09 00:00:18 |       5528.48 |
| 2025-03-08 00:00:17 |       5521.71 |
| 2025-03-07 00:00:11 |       5515.27 |
| 2025-03-06 00:00:14 |       5510.17 |
| 2025-03-05 00:00:06 |       5504.08 |
| 2025-03-04 00:00:09 |       5497.21 |
+---------------------+---------------+

这是结果，因为我每 20 秒写入一次值。我选择了当天的第一个值，所以现在我需要天数之间的差异。我尝试使用 LAG() 函数，但我不知道如何将该函数纳入常规查询。

最好的问候弗兰克

mariadb

3 个回答

  1. 您的查询利用了臭名昭著的 MySQL 扩展 GROUP BY。这意味着一堆令人困惑的不确定性。

    SELECT created_at, ANY_VALUE(verbrauch_ges)
  FROM pv_metrics
 group by DATE(created_at)
 ORDER BY ANY_VALUE(ID) DESC limit 20

    ANY_VALUE)_告诉 MySQL 它可以任意从组中选择某个值。这样你将得到不可预测的值。

    这使得我们很难从您的示例中猜测您的意图。

    听起来你想要一个每个日历日一行的结果集，显示当天verbrauch_ges最早日期戳的值之间的差异created_at

    以下是具体操作方法。

    created_at首先，您需要获得每天最早时间的结果集。

    SELECT MIN(created_at) created_at
  FROM pv_metrics
 GROUP BY DATE(created_at)

    然后您需要每天提取一个值。

    SELECT DATE(pv_metrics.created_at) tag, AVG(verbracht_ges) erste_verbraucht_ges 
  FROM pv_metrics
  JOIN (
        SELECT MIN(created_at) created_at
         FROM pv_metrics
        GROUP BY DATE(created_at)
       ) firsts ON pv_metrics.created_at = firsts.created_at
  GROUP_BY DATE(pv_metrics.created_at)

    如果您在同一时间有多个观察结果，则可以使用 AVG() 来做一些合理的操作created_at。您也可以使用 MAX() 或 MIN()。

    最后，现在您有一个带有列的虚拟表tag，erste_verbraucht_ges 您可以对其执行 LAG 选择来获得所需的结果。

    • 0
  2. Best Answer

    在简单情况下，
    如果我们认为的值verbrauch_ges单调递增，则可以按如下方式获得当天的最小（初始）值

    SELECT min(created_at)created_at
  ,min(verbrauch_ges) verbrauch_ges
  ,lag(min(verbrauch_ges))over(order by created_at) prev_verbauch_ges
  ,min(verbrauch_ges)-coalesce(lag(min(verbrauch_ges))over(order by created_at),0.0) as day_diff
FROM pv_metrics 
group by DATE(created_at) 
ORDER BY ID DESC limit 20;

    对于 LAG 函数，我们使用值min(verbrauch_ges)，因为 SELECT 子句中的 LAG 函数将在 GROUP BY 之后执行。
    这在语义上等同于

    SELECT created_at, verbrauch_ges
  ,lag(verbrauch_ges)over(order by created_at) prev_verbauch_ges
FROM (
  SELECT min(created_at) as created_at, min(verbrauch_ges) as verbrauch_ges
  FROM pv_metrics 
  group by DATE(created_at) 
  ORDER BY ID DESC limit 20
)sq
;

    MariaDb 允许使用order by created_atLAGorder by date(created_at)功能。

    常见情况是从表和 INNER JOIN 表中选择目标行到此选择

    SELECT created_at, verbrauch_ges
  ,lag(verbrauch_ges)over(order by created_at) prev_verbauch_ges
FROM (
   select min(id) id
   from pv_metrics  
   group by DATE(created_at)
   ORDER BY min(id) DESC limit 20
) s
inner join pv_metrics m on m.id=s.id
ORDER BY s.ID DESC ;

    或者

    SELECT created_at, verbrauch_ges
  ,lag(verbrauch_ges)over(order by created_at) prev_verbauch_ges
FROM (
   select min(created_at) created_at
   from pv_metrics  
   group by DATE(created_at)
   ORDER BY min(id) DESC limit 20
) s
inner join pv_metrics m on m.created_at=s.created_at
ORDER BY s.ID DESC ;

    示例

    • 0
  3. 创建于 动词 上一页 day_diff
    2025-03-23 00:00:04 5628.79 5622.54345703125 6.2431640625
    2025-03-22 00:00:06 5622.54 5615.43798828125 7.10546875
    2025-03-21 00:00:11 5615.44 5609.4912109375 5.94677734375
    2025-03-20 00:00:13 5609.49 5605.40380859375 4.08740234375
    2025-03-19 00:00:17 5605.4 5600.44970703125 4.9541015625
    2025-03-18 00:00:00 5600.45 5591.36279296875 9.0869140625
    2025-03-17 00:00:03 5591.36 5585.2001953125 6.16259765625
    2025-03-16 00:00:09 5585.2 5578.9580078125 6.2421875
    2025-03-15 00:00:16 5578.96 5571.10498046875 7.85302734375
    2025-03-14 00:00:04 5571.1 5560.3427734375 10.76220703125
    2025-03-13 00:00:07 5560.34 5550.5400390625 9.802734375
    2025-03-12 00:00:10 5550.54 5540.1171875 10.4228515625
    2025-03-11 00:00:13 5540.12 5534.80908203125 5.30810546875
    2025-03-10 00:00:16 5534.81 5528.48046875 6.32861328125
    2025-03-09 00:00:18 5528.48 5521.70703125 6.7734375
    2025-03-08 00:00:17 5521.71 5515.27392578125 6.43310546875
    2025-03-07 00:00:11 5515.27 5510.17431640625 5.099609375
    2025-03-06 00:00:14 5510.17 5504.083984375 6.09033203125
    2025-03-05 00:00:06 5504.08 5497.21240234375 6.87158203125
    2025-03-04 00:00:09 5497.21 5489.51904296875 7.693359375 
    SELECT min(created_at)created_at
  ,min(verbrauch_ges) verbrauch_ges
  ,lag(min(verbrauch_ges))over(order by created_at) prev_verbauch_ges
  ,min(verbrauch_ges)-coalesce(lag(min(verbrauch_ges))over(order by created_at),0.0) as day_diff
FROM pv_metrics 
group by DATE(created_at) 
ORDER BY ID DESC limit 20;

    这就是我需要的答案或疑问。

    谢谢

    • 0

相关问题