first_value
如果您使用的是商业数据库或PostgreSQL / Firebird / CUBRID,则可以利用窗口功能的全部功能。 我们已经写了几次关于窗口函数的很棒的博客 ,特别是关于ROW_NUMBER(),RANK()和DENSE_RANK()的 博客 。
今天,我们将研究一些很棒的窗口函数,这些函数会产生位于当前行之前或之后的其他行的值。
设置测试数据
今天,我们将使用世界银行的公开数据进行一些有趣的统计。 为简单起见,我们仅对G8国家 /地区进行分析:
- 加拿大(CA)
- 法国(FR)
- 德国(DE)
- 意大利(IT)
- 日本(JP)
- 俄罗斯(RU)
- 英国(GB)
- 美国(美国)
对于这些国家/地区,让我们考虑以下2009-2012年的数据点:
人均GDP(现价美元)
2009 2010 2011 2012
CA 40,764 47,465 51,791 52,409
DE 40,270 40,408 44,355 42,598
FR 40,488 39,448 42,578 39,759
GB 35,455 36,573 38,927 38,649
IT 35,724 34,673 36,988 33,814
JP 39,473 43,118 46,204 46,548
RU 8,616 10,710 13,324 14,091
US 46,999 48,358 49,855 51,755
中央政府债务,总计(占GDP的百分比)
2009 2010 2011 2012
CA 51.3 51.4 52.5 53.5
DE 47.6 55.5 55.1 56.9
FR 85.0 89.2 93.2 103.8
GB 71.7 85.2 99.6 103.2
IT 121.3 119.9 113.0 131.1
JP 166.8 174.8 189.5 196.5
RU 8.7 9.1 9.3 9.4
US 76.3 85.6 90.1 93.8
让我们将所有这些数据放入事实表中,如下所示(PostgreSQL语法):
CREATE TABLE countries (
code CHAR(2) NOT NULL,
year INT NOT NULL,
gdp_per_capita DECIMAL(10, 2) NOT NULL,
govt_debt DECIMAL(10, 2) NOT NULL
);
INSERT INTO countries
VALUES ('CA', 2009, 40764, 51.3),
('CA', 2010, 47465, 51.4),
('CA', 2011, 51791, 52.5),
('CA', 2012, 52409, 53.5),
('DE', 2009, 40270, 47.6),
('DE', 2010, 40408, 55.5),
('DE', 2011, 44355, 55.1),
('DE', 2012, 42598, 56.9),
('FR', 2009, 40488, 85.0),
('FR', 2010, 39448, 89.2),
('FR', 2011, 42578, 93.2),
('FR', 2012, 39759,103.8),
('GB', 2009, 35455,121.3),
('GB', 2010, 36573, 85.2),
('GB', 2011, 38927, 99.6),
('GB', 2012, 38649,103.2),
('IT', 2009, 35724,121.3),
('IT', 2010, 34673,119.9),
('IT', 2011, 36988,113.0),
('IT', 2012, 33814,131.1),
('JP', 2009, 39473,166.8),
('JP', 2010, 43118,174.8),
('JP', 2011, 46204,189.5),
('JP', 2012, 46548,196.5),
('RU', 2009, 8616, 8.7),
('RU', 2010, 10710, 9.1),
('RU', 2011, 13324, 9.3),
('RU', 2012, 14091, 9.4),
('US', 2009, 46999, 76.3),
('US', 2010, 48358, 85.6),
('US', 2011, 49855, 90.1),
('US', 2012, 51755, 93.8);
开始有趣的查询
习惯了SQL-92语法的人将能够从表中快速找到最高的人均GDP或最高的债务。 这样的查询很简单:
SELECT MAX(gdp_per_capita), MAX(govt_debt)
FROM countries;
哪个会返回:
52409.00 196.50
但这并不有趣。 我们甚至不知道这些价值观与哪些国家和年份相关。
返回所有这些值的标准SQL-92(以及标准关系)查询如下所示:
SELECT
'highest gdp per capita' AS what,
c1.*
FROM countries c1
WHERE NOT EXISTS (
SELECT 1
FROM countries c2
WHERE c1.gdp_per_capita < c2.gdp_per_capita
)
UNION ALL
SELECT
'highest government debt' AS what,
c1.*
FROM countries c1
WHERE NOT EXISTS (
SELECT 1
FROM countries c2
WHERE c1.govt_debt < c2.govt_debt
)
本质上,对于gdp_per_capita
(第一子选择)或govt_debt
(第二子选择),我们选择不存在其他任何具有更高值的行。
招! 使用量化的比较谓词!
如果您的数据库支持量化的比较谓词 ,那么您可以这样编写得更简洁一些:
SELECT
'highest gdp per capita' AS what,
countries.*
FROM countries
WHERE gdp_per_capita >= ALL (
SELECT gdp_per_capita FROM countries
)
UNION ALL
SELECT
'highest government debt' AS what,
countries.*
FROM countries
WHERE govt_debt >= ALL (
SELECT govt_debt FROM countries
)
基本上与…相同
SELECT
'highest gdp per capita' AS what,
countries.*
FROM countries
WHERE gdp_per_capita = (
SELECT MAX(gdp_per_capita) FROM countries
)
UNION ALL
SELECT
'highest government debt' AS what,
countries.*
FROM countries
WHERE govt_debt = (
SELECT MAX(govt_debt) FROM countries
)
输出为:
what code year gdp debt
----------------------------------------------------
highest gdp per capita CA 2012 52409.00 53.50
highest government debt JP 2012 46548.00 196.50
大量SQL仅具有很少的分析功能,并且以某种方式,使用所有这些子选择四次查询同一张表并不完全正确!
FIRST_VALUE()和LAST_VALUE()
这是窗口函数起作用的地方,在这种情况下,就是FIRST_VALUE()
或LAST_VALUE()
。 现在,让我们着重从数据集中计算最大的人均GDP:
SELECT
countries.*,
FIRST_VALUE (code) OVER (w_gdp) AS max_gdp_code,
FIRST_VALUE (year) OVER (w_gdp) AS max_gdp_year,
FIRST_VALUE (gdp_per_capita) OVER (w_gdp) AS max_gdp_gdp,
FIRST_VALUE (govt_debt) OVER (w_gdp) AS max_gdp_debt
FROM
countries
WINDOW
w_gdp AS (ORDER BY gdp_per_capita DESC)
ORDER BY
code, year
请注意,我们是如何使用SQL标准WINDOW
子句的,而PostgreSQL和Sybase SQL Anywhere目前仅支持该子句。
如果您正在使用Oracle或任何其他商业数据库,则可以简单地将窗口引用w_gdp
为各种OVER()
子句以实现相同的行为– 或可以使用jOOQ的WINDOW子句支持并让jOOQ为您做同样的事情。
上面的查询不会产生任何汇总,但会将人均GDP最高的国家/年的值添加到表中的每一行:
each country highest per year
-----------------------------------------------
CA 2009 40764.00 51.30 CA 2012 52409.00 53.50
CA 2010 47465.00 51.40 CA 2012 52409.00 53.50
CA 2011 51791.00 52.50 CA 2012 52409.00 53.50
CA 2012 52409.00 53.50 CA 2012 52409.00 53.50
这非常有趣,因为数据尚未聚合–原始数据集保持不变,并使用新的计算列进行了充实。
然后,您可以进一步处理事情,例如,比较每个国家/年的人均GDP最高和该国家/年的人均GDP最高的债务:
SELECT
countries.*,
TO_CHAR(100 * gdp_per_capita / FIRST_VALUE (gdp_per_capita) OVER (w_gdp) , '999.99 %') gdp_rank,
TO_CHAR(100 * govt_debt / FIRST_VALUE (govt_debt) OVER (w_debt), '999.99 %') debt_rank
FROM
countries
WINDOW
w_gdp AS (PARTITION BY year ORDER BY gdp_per_capita DESC),
w_debt AS (PARTITION BY year ORDER BY govt_debt DESC)
ORDER BY
code, year
注意如何将PARTITION BY
添加到WINDOW
子句的窗口定义中。 之所以这样做,是因为我想按年份对数据集进行分区,以便找到每年而不是整个数据集的最高GDP /债务值。
然后,可以在此处查看以上查询的结果:
country percentages
------------------------------------------
CA 2009 40764 51.3 86.73% 30.76%
CA 2010 47465 51.4 98.15% 29.41%
CA 2011 51791 52.5 100.00% 27.70%
CA 2012 52409 53.5 100.00% 27.23%
DE 2009 40270 47.6 85.68% 28.54%
DE 2010 40408 55.5 83.56% 31.75%
DE 2011 44355 55.1 85.64% 29.08%
DE 2012 42598 56.9 81.28% 28.96%
FR 2009 40488 85.0 86.15% 50.96%
FR 2010 39448 89.2 81.57% 51.03%
FR 2011 42578 93.2 82.21% 49.18%
FR 2012 39759 103.8 75.86% 52.82%
GB 2009 35455 121.3 75.44% 72.72%
GB 2010 36573 85.2 75.63% 48.74%
GB 2011 38927 99.6 75.16% 52.56%
GB 2012 38649 103.2 73.74% 52.52%
IT 2009 35724 121.3 76.01% 72.72%
IT 2010 34673 119.9 71.70% 68.59%
IT 2011 36988 113.0 71.42% 59.63%
IT 2012 33814 131.1 64.52% 66.72%
JP 2009 39473 166.8 83.99% 100.00%
JP 2010 43118 174.8 89.16% 100.00%
JP 2011 46204 189.5 89.21% 100.00%
JP 2012 46548 196.5 88.82% 100.00%
RU 2009 8616 8.7 18.33% 5.22%
RU 2010 10710 9.1 22.15% 5.21%
RU 2011 13324 9.3 25.73% 4.91%
RU 2012 14091 9.4 26.89% 4.78%
US 2009 46999 76.3 100.00% 45.74%
US 2010 48358 85.6 100.00% 48.97%
US 2011 49855 90.1 96.26% 47.55%
US 2012 51755 93.8 98.75% 47.74%
可以说,在八国集团国家中,加拿大确实是过去几年中进步最大的国家,与全球国内生产总值相比,其债务减少了,同时,全球全球人均GDP却增加了。
除了按年划分数据集,我们还可以按国家划分数据,并找到这些年来每个国家的最佳/最差年份:
SELECT
countries.*,
TO_CHAR(100 * gdp_per_capita / FIRST_VALUE (gdp_per_capita) OVER (w_gdp), '999.99 %') gdp_rank,
TO_CHAR(100 * govt_debt / FIRST_VALUE (govt_debt) OVER (w_debt), '999.99 %') debt_rank
FROM
countries
WINDOW
w_gdp AS (PARTITION BY code ORDER BY gdp_per_capita DESC),
w_debt AS (PARTITION BY code ORDER BY govt_debt DESC)
ORDER BY
code, year
现在的结果看起来将完全不同:
country percentages
------------------------------------------
CA 2009 40764 51.3 77.78% 95.89%
CA 2010 47465 51.4 90.57% 96.07%
CA 2011 51791 52.5 98.82% 98.13%
CA 2012 52409 53.5 100.00% 100.00%
DE 2009 40270 47.6 90.79% 83.66%
DE 2010 40408 55.5 91.10% 97.54%
DE 2011 44355 55.1 100.00% 96.84%
DE 2012 42598 56.9 96.04% 100.00%
FR 2009 40488 85.0 95.09% 81.89%
FR 2010 39448 89.2 92.65% 85.93%
FR 2011 42578 93.2 100.00% 89.79%
FR 2012 39759 103.8 93.38% 100.00%
GB 2009 35455 121.3 91.08% 100.00%
GB 2010 36573 85.2 93.95% 70.24%
GB 2011 38927 99.6 100.00% 82.11%
GB 2012 38649 103.2 99.29% 85.08%
IT 2009 35724 121.3 96.58% 92.52%
IT 2010 34673 119.9 93.74% 91.46%
IT 2011 36988 113.0 100.00% 86.19%
IT 2012 33814 131.1 91.42% 100.00%
JP 2009 39473 166.8 84.80% 84.89%
JP 2010 43118 174.8 92.63% 88.96%
JP 2011 46204 189.5 99.26% 96.44%
JP 2012 46548 196.5 100.00% 100.00%
RU 2009 8616 8.7 61.15% 92.55%
RU 2010 10710 9.1 76.01% 96.81%
RU 2011 13324 9.3 94.56% 98.94%
RU 2012 14091 9.4 100.00% 100.00%
US 2009 46999 76.3 90.81% 81.34%
US 2010 48358 85.6 93.44% 91.26%
US 2011 49855 90.1 96.33% 96.06%
US 2012 51755 93.8 100.00% 100.00%
如您所见,多年来,大多数国家/地区的人均GDP总体上表现较好,而且大多数国家/地区几乎都严格增加了自己的GDP债务(德国,法国和意大利除外),王国)。 俄罗斯和加拿大增长最快。
在以上示例中,我们主要使用了FIRST_VALUE()
。 LAST_VALUE()
与排序几乎是相反的函数,就像MAX()
是MIN()
的相反函数一样。 我说这几乎是因为在将LAST_VALUE()
与ORDER BY
一起使用时存在警告,因为使用ORDER BY
的窗口定义与使用ORDER BY
和所谓的“框架子句”的窗口定义隐式等效:
-- Find the "last" year over the complete data set
-- This may not behave as expected, so always provide
-- an explicit ORDER BY clause
LAST_VALUE (year) OVER()
-- These two are implicitly equivalent. We're not
-- looking for the "last" year in the complete data
-- set, but only in the frame that is "before" the
-- current row. In other words, the current row is
-- always the "last value"!
LAST_VALUE (year) OVER(ORDER BY year)
LAST_VALUE (year) OVER(
ORDER BY year
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
-- Find the "last" year in the complete data set with
-- explicit ordering
LAST_VALUE (year) OVER(
ORDER BY year
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)
LEAD()和LAG()
先前的功能是将值与数据集中的最大值/最小值( FIRST_VALUE()
和LAST_VALUE()
)进行比较。 但是,使用窗口函数,您还可以将内容与下一个/上一个值进行比较。 或第二倒数第二个/第二个倒数,等等。用于此的函数称为LEAD()
(用于下一个值)和LAG()
(用于前一个值)。
最好用示例说明:
-- Use this view as a data source containing
-- all the distinct years: 2009-2012
WITH years AS (
SELECT DISTINCT year
FROM countries
)
SELECT
FIRST_VALUE (year) OVER w_year AS first,
LEAD (year, 2) OVER w_year AS lead2,
LEAD (year) OVER w_year AS lead1,
year,
LAG (year) OVER w_year AS lag1,
LAG (year, 2) OVER w_year AS lag2,
LAST_VALUE (year) OVER w_year AS last
FROM
years
WINDOW
w_year AS (
ORDER BY year DESC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)
ORDER BY year
现在的结果是:
first lead2 lead1 year lag1 lag2 last
----------------------------------------------
2012 2009 2010 2011 2009
2012 2009 2010 2011 2012 2009
2012 2009 2010 2011 2012 2009
2012 2010 2011 2012 2009
LEAD()
和LAG()
实际上是最好的窗口函数,可以帮助您理解窗口函数的整个概念。 对于每一年,您可以立即看到如何使用非常简单的函数调用在相同的窗口和框架中生成上一年和下一年。
例如,可以使用它来查找每个国家/年人均GDP的“邻国”:
SELECT
year,
code,
gdp_per_capita,
LEAD (code) OVER w_gdp AS runner_up_code,
LEAD (gdp_per_capita) OVER w_gdp AS runner_up_gdp,
LAG (code) OVER w_gdp AS leader_code,
LAG (gdp_per_capita) OVER w_gdp AS leader_gdp
FROM
countries
WINDOW
w_gdp AS (PARTITION BY year ORDER BY gdp_per_capita DESC)
ORDER BY year DESC, gdp_per_capita DESC
哪个返回:
year country runner-up leader
------------------------------------------
2012 CA 52409 US 51755
2012 US 51755 JP 46548 CA 52409
2012 JP 46548 DE 42598 US 51755
2012 DE 42598 FR 39759 JP 46548
2012 FR 39759 GB 38649 DE 42598
2012 GB 38649 IT 33814 FR 39759
2012 IT 33814 RU 14091 GB 38649
2012 RU 14091 IT 33814
2011 CA 51791 US 49855
2011 US 49855 JP 46204 CA 51791
2011 JP 46204 DE 44355 US 49855
2011 DE 44355 FR 42578 JP 46204
2011 FR 42578 GB 38927 DE 44355
2011 GB 38927 IT 36988 FR 42578
2011 IT 36988 RU 13324 GB 38927
2011 RU 13324 IT 36988
2010 US 48358 CA 47465
2010 CA 47465 JP 43118 US 48358
2010 JP 43118 DE 40408 CA 47465
2010 DE 40408 FR 39448 JP 43118
2010 FR 39448 GB 36573 DE 40408
2010 GB 36573 IT 34673 FR 39448
2010 IT 34673 RU 10710 GB 36573
2010 RU 10710 IT 34673
2009 US 46999 CA 40764
2009 CA 40764 FR 40488 US 46999
2009 FR 40488 DE 40270 CA 40764
2009 DE 40270 JP 39473 FR 40488
2009 JP 39473 IT 35724 DE 40270
2009 IT 35724 GB 35455 JP 39473
2009 GB 35455 RU 8616 IT 35724
2009 RU 8616 GB 35455
如果您想进行更多的分析,现在可以比较领导者和亚军之间的百分比。 在本文中可以看到LEAD()
和LAG()
另一个很好的用例 。
结论
窗口功能是一个非常强大的功能,可以从所有主要的商业数据库以及几个开源数据库(如PostgreSQL,Firebird和CUBRID)中获得。 实际上, 在窗口函数之前存在SQL ,而在窗口函数之后存在 SQL 。
使用jOOQ ,您可以像其他与SQL有关的其他内容一样,在类型安全级别上利用窗口函数。 我们看到的最后一个查询可以这样写:
// Static import the generated tables and all
// of jOOQ's functions from DSL
import static org.jooq.example.db.postgres.Tables.*;
import static org.jooq.impl.DSL.*;
// Shorten the table reference by aliasing
Countries c = COUNTRIES;
// Specifiy a window definition
WindowDefinition w_gdp =
name("w_gdp").as(
partitionBy(c.YEAR)
.orderBy(c.GDP_PER_CAPITA.desc()
)
);
// Write the query as if it were native SQL
System.out.println(
DSL.using(conn)
.select(
c.YEAR,
c.CODE,
c.GDP_PER_CAPITA,
lead(c.CODE) .over(w_gdp).as("runner_up_code"),
lead(c.GDP_PER_CAPITA).over(w_gdp).as("runner_up_gdp"),
lag (c.CODE) .over(w_gdp).as("leader_code"),
lag (c.GDP_PER_CAPITA).over(w_gdp).as("leader_gdp")
)
.from(c)
.window(w_gdp)
.orderBy(c.YEAR.desc(), c.GDP_PER_CAPITA.desc())
.fetch()
);
上面的程序将输出
+----+----+--------------+--------------+-------------+-----------+----------+
|year|code|gdp_per_capita|runner_up_code|runner_up_gdp|leader_code|leader_gdp|
+----+----+--------------+--------------+-------------+-----------+----------+
|2012|CA | 52409.00|US | 51755.00|{null} | {null}|
|2012|US | 51755.00|JP | 46548.00|CA | 52409.00|
|2012|JP | 46548.00|DE | 42598.00|US | 51755.00|
|2012|DE | 42598.00|FR | 39759.00|JP | 46548.00|
|2012|FR | 39759.00|GB | 38649.00|DE | 42598.00|
|2012|GB | 38649.00|IT | 33814.00|FR | 39759.00|
|2012|IT | 33814.00|RU | 14091.00|GB | 38649.00|
|2012|RU | 14091.00|{null} | {null}|IT | 33814.00|
|2011|CA | 51791.00|US | 49855.00|{null} | {null}|
|2011|US | 49855.00|JP | 46204.00|CA | 51791.00|
|2011|JP | 46204.00|DE | 44355.00|US | 49855.00|
|2011|DE | 44355.00|FR | 42578.00|JP | 46204.00|
|2011|FR | 42578.00|GB | 38927.00|DE | 44355.00|
|2011|GB | 38927.00|IT | 36988.00|FR | 42578.00|
|2011|IT | 36988.00|RU | 13324.00|GB | 38927.00|
|2011|RU | 13324.00|{null} | {null}|IT | 36988.00|
|2010|US | 48358.00|CA | 47465.00|{null} | {null}|
|2010|CA | 47465.00|JP | 43118.00|US | 48358.00|
|2010|JP | 43118.00|DE | 40408.00|CA | 47465.00|
|2010|DE | 40408.00|FR | 39448.00|JP | 43118.00|
|2010|FR | 39448.00|GB | 36573.00|DE | 40408.00|
|2010|GB | 36573.00|IT | 34673.00|FR | 39448.00|
|2010|IT | 34673.00|RU | 10710.00|GB | 36573.00|
|2010|RU | 10710.00|{null} | {null}|IT | 34673.00|
|2009|US | 46999.00|CA | 40764.00|{null} | {null}|
|2009|CA | 40764.00|FR | 40488.00|US | 46999.00|
|2009|FR | 40488.00|DE | 40270.00|CA | 40764.00|
|2009|DE | 40270.00|JP | 39473.00|FR | 40488.00|
|2009|JP | 39473.00|IT | 35724.00|DE | 40270.00|
|2009|IT | 35724.00|GB | 35455.00|JP | 39473.00|
|2009|GB | 35455.00|RU | 8616.00|IT | 35724.00|
|2009|RU | 8616.00|{null} | {null}|GB | 35455.00|
+----+----+--------------+--------------+-------------+-----------+----------+
无论您是使用jOOQ进行数据库集成,还是仅使用普通SQL,今天就开始使用窗口函数。
first_value