sql - 优化 LAG 查询的输出

Question

您好，我在 Ubuntu 上运行 postgresql 10.10。

我的表中有以下数据：

  radio  | hits | iteration
---------+------+-----------
     211 |    1 |         1
 1423013 |    1 |         1
 2114805 |    1 |         1
 2114833 |    1 |         1
 2109257 |    1 |         1
  126681 |    1 |         2
  802802 |    2 |         2
 1426801 |    2 |         2
 2613847 |    1 |         2
     208 |   27 |         2
(10 rows)

 radio | hits | iteration
-------+------+-----------
   211 |    1 |         1
   211 |   24 |         2
   211 |   49 |         3
   211 |   84 |         4
   211 |  131 |         5
   211 |  157 |         6
   211 |  169 |         7
   211 |  181 |         8
   211 |  200 |         9
   211 |  223 |        10
   211 |  244 |        11
   211 |  273 |        12

hits 列是累积的，我需要从当前迭代命中中减去以前的迭代命中以确定值的变化。目前我正在使用以下 LAG 查询。

SELECT radio_alias, iteration, radio, hits - LAG ( hits, 1, 0 ) 
OVER (PARTITION BY radio ORDER BY iteration) AS ithits FROM radios;

这给了我以下输出，这是我所期望的：

 radio_alias | iteration |  radio  | ithits
-------------+-----------+---------+--------
 ""          |       101 |     205 |      0
 ""          |       102 |     205 |      7
 ""          |       103 |     205 |      2
 ""          |         2 |     208 |     27
 ""          |         3 |     208 |     38
 ""          |         4 |     208 |     13
 ""          |         5 |     208 |     21

我需要做的是从 LAG 查询的输出中选择所有具有最大迭代值的行，理想情况下 SQL 语句会自动确定它，比如 MAX(iteration)，如果不是，我可以从我的程序中提供一个变量。

我已经能够使用以下代码使用临时表来解决它，但想知道是否有更优雅的解决方案不涉及临时表。

SELECT radio_alias, iteration, radio, hits - LAG ( hits, 1, 0 ) 
OVER (PARTITION BY radio ORDER BY iteration) AS ithits INTO TEMP rad_hits FROM radios;

SELECT * FROM rad_hits WHERE iteration = 103 ORDER BY ithits DESC LIMIT 5;

 radio_alias | iteration |  radio  | ithits
-------------+-----------+---------+--------
 ""          |       103 |     209 |     41
 ""          |       103 |     211 |     29
 ""          |       103 | 2109215 |     20
 ""          |       103 |     210 |     18
 ""          |       103 | 2109232 |     17
(5 rows)

标签： sqlpostgresql

Answer 1

这是做你想做的吗？

SELECT radio_alias, iteration, radio,
       hits - LAG ( hits, 1, 0 ) OVER (PARTITION BY radio ORDER BY iteration) AS ithits
FROM radios
ORDER BY iteration DESC
LIMIT 5;