在 DB2 中检索一天中所有时间(1 小时分辨率)而不需要后备表的优雅方法是什么?
我正在从 DB2/z 中的表中查询一些数据,该表保存每小时数据,但偶尔会跳过一些小时(如果客户端没有发送该小时的详细信息)。
由于我将数据直接输入到需要查询来获取轴数据的图表工具中,因此有时会缺少时间轴上的值并且看起来很难看:
23.00 |===
22.00 |=====
14.00 |============
01.00 |==
00.00 |=
+--------------------
我们已禁止添加任何辅助表(例如 24 行)表保存了所有时间,因此我需要在 DB2 中进行一个查询,该查询将返回一天中的所有时间,而无需可靠的后备表。我一直在使用 monstrous:
select '00.00' from sysibm.sysdummy1
union all select '01.00' from sysibm.sysdummy1
union all select '02.00' from sysibm.sysdummy1
union all select '03.00' from sysibm.sysdummy1
: : :
union all select '23.00' from sysibm.sysdummy1
order by 1
这是我见过的最笨拙的解决方案,所以我宁愿有一些更干净的东西。
我在下面发布了我自己的解决方案,但我愿意接受任何更简单或更优雅的解决方案。
I'm querying some data from a table in DB2/z which holds hourly data but occasionally skips some hours (if the clients don't send up details for that hour).
Since I'm feeding the data straight into a charting tool which needs a query to get the axis data, it sometimes has missing values from the time axis and looks ugly:
23.00 |===
22.00 |=====
14.00 |============
01.00 |==
00.00 |=
+--------------------
We've been verboten from adding any helper tables like a 24-row table holding all the hours so I need a query in DB2 that will return all the hours of the day without a reliable backing table. I've been using the monstrous:
select '00.00' from sysibm.sysdummy1
union all select '01.00' from sysibm.sysdummy1
union all select '02.00' from sysibm.sysdummy1
union all select '03.00' from sysibm.sysdummy1
: : :
union all select '23.00' from sysibm.sysdummy1
order by 1
which is about as kludgy a solution I've ever seen, so I'd rather have something a little cleaner.
I've posted my own solution below but I'm open to anything simpler or more elegant.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
以下级别限制的递归调用将给出所需的范围,而无需支持表。如果没有级别限制,DB2 会抱怨该函数可能会无限递归。
这给出:
根据需要。
The following level-limited recursive call will give the desired range without a backing table. Without the limit on level, DB2 complains that the function may be infinitely recursive.
This gives:
as desired.
应该也有效,至少对我来说(在 LUW 9.1+ 上)。 :-) 仍然很笨拙,但更紧凑。
一般来说,我发现如果您不需要
SELECT
的全部功能,也不需要命名您的列,VALUES
是一个更简单的替代方案。如果您确实想命名列,只需将
VALUES
放入SELECT
中即可:(对于某些版本的 DB2,
foo
是可选的。 )哦,哇,我刚刚阅读了
VALUES
的 DB2 z/OS 文档,与 LUW 版本相比,它要弱得多。对于 z/OS,VALUES
只能在触发器的触发操作中使用。对不起,帕克斯迪亚布罗。 :-(should work too, at least for me (on LUW 9.1+). :-) Still kludgey, but more compact.
In general, I've found that if you don't need the full power of
SELECT
, and don't need to name your columns,VALUES
is a much simpler alternative.If you do want to name your column, just throw the
VALUES
within aSELECT
:(With some versions of DB2, the
foo
is optional.)Oh wow, I've just read the DB2 z/OS documentation for
VALUES
, and it's much wimpier compared to the LUW version. For z/OS,VALUES
can only be used in the triggered action of a trigger. Sorry, paxdiablo. :-(