我们不是都在想:
我怎样才能做到这一点? 我在Excel中有这些数据,我想进行分组/排序/分配/合并...
尽管您可能会拉起Visual Basic脚本来执行工作或将数据导出到Java或其他任何选择的过程语言,但为什么不仅仅使用SQL?
用例:计算体育场编排中的相邻颜色
对于许多人来说,这可能不是日常用例,但对于FanPictor的办公室朋友来说 ,却是。 他们正在创建软件以将粉丝编排直接吸引到体育场。 这里是高层次的用例:
立即知道这个有趣的软件是做什么的,对吗?
- 您提交了编舞建议
- 活动组织者选择最佳作品
- 活动组织者将编排导出为Excel文件
- Excel文件将送入印刷厂,以打印红色/红色,红色/白色,白色/红色,白色/白色面板(或任何其他颜色)
- 活动助手将彩色面板分配到适当的座位上
- 粉丝们都很兴奋
看看Excel电子表格
这就是Excel电子表格的样子:
现在,分发这些面板是愚蠢的,重复的工作。 根据经验,我们在FanPictor的朋友希望有一些与此类似的东西,而不是:
请注意,每个面板都有相关的说明以指示:
- …连续连续的相同面板的开始还是停止
- …这样一排有多少个相同的面板
“连续”是指在体育场的扇形区和排区中,相邻的座位具有相同的(Scene1, Scene2)
元组。
我们如何解决这个问题?
我们当然可以通过SQL来解决此问题 –并通过一个像样的数据库来支持窗口功能 (例如PostgreSQL )或您选择的任何商业数据库! ( 您不会在MySQL中找到这种功能 )。
这是查询:
with data
as (
select
d.*,
row(sektor, row, scene1, scene2) block
from d
)
select
sektor,
row,
seat,
scene1,
scene2,
case
when lag (block) over(o) is distinct from block
and lead(block) over(o) is distinct from block
then 'start / stop'
when lag (block) over(o) is distinct from block
then 'start'
when lead(block) over(o) is distinct from block
then 'stop'
else ''
end start_stop,
count(*) over(
partition by sektor, row, scene1, scene2
) cnt
from data
window o as (
order by sektor, row, seat
)
order by sektor, row, seat;
而已! 不太难,是吗?
让我们详细介绍几个细节。 我们使用了许多很棒的SQL标准/ PostgreSQL概念,这些值得解释:
行值构造函数
ROW()
值构造函数是一项非常强大的功能,可用于将多个列(或行)组合为单个ROW
/ RECORD
类型:
row(sektor, row, scene1, scene2) block
然后,此类型可用于行值比较 ,从而节省大量时间逐列比较。
DISTINCT谓词
lag (block) over(o) is distinct from block
通过使用DISTINCT谓词将上述窗口函数的结果与先前构造的ROW
进行比较,这是在SQL中比较“空安全”的好方法。 请记住, SQL NULL是SQL中最难解决的问题 。
视窗功能
窗口功能是一个非常棒的概念。 如果没有任何GROUP BY
子句,则可以在投影SELECT
子句时在当前行的上下文中计算聚合函数,窗口函数,排名函数等。 例如:
count(*) over(
partition by sektor, row, scene1, scene2
) cnt
在给定分区标准的情况下,上述窗口函数对与当前行位于同一分区(“组”)中的所有行进行计数。 换句话说,所有具有相同(scene1, scene2)
颜色并且位于相同(sector, row)
。
其他窗口函数是lead
和lag
,它们给定特定顺序从上一行或下一行返回一个值:
lag (block) over(o),
lead(block) over(o)
-- ...
window o as (
order by sektor, row, seat
)
还要注意使用SQL标准WINDOW
子句 ,只有PostgreSQL和Sybase SQL Anywhere支持此子句 。
在上面的代码段中, lag()
返回给定顺序o
的前一行的block
值,而lead()
将返回下一行的block
值或NULL
,在这种情况下,我们很高兴使用DISTINCT谓词,之前。
请注意,您还可以选择提供其他数字参数,以指示您要向后或向前访问第二,第三,第五或第八行。
SQL是您功能最强大且被低估的工具
在Data Geekery ,我们总是说
SQL是仅凭其强大功能才能揭开神秘面纱的设备
如果您一直在关注我们的博客,那么您可能已经注意到,我们试图将SQL宣传为Java开发人员的一流头等公民。 jOOQ支持上述大多数功能,如果不可用,则会转换为您的本机SQL方言。
因此,如果您还没有听过,请听听Peter Kopfler,他在我们最近在维也纳进行的jOOQ / SQL谈话后非常激动,以至于他现在都在研究标准和使用PostgreSQL:
介意弯曲谈话@lukaseder约@JavaOOQ在今晚的@jsugtu 。 我的新决议:安装PostgreSQL并立即学习SQL标准。
—彼得·科夫勒(@codecopkofler) 2014年4月7日
进一步阅读
翻译自: https://www.javacodegeeks.com/2014/04/how-can-i-do-this-with-sql-of-course.html