- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
这是 this 的后续行动问题,我的查询改进为使用窗口函数而不是 LATERAL
中的聚合加入。虽然查询现在快了很多,但我发现结果不正确。
我需要对 x 年的跟踪时间范围执行计算。例如,price_to_maximum_earnings
通过获取 max(earnings)
每行计算十多年前到当前行,并划分price
由结果。为简单起见,我们将在这里使用 1 年。
SQL Fiddle对于这个问题。 (Postgres 9.6)
作为一个简单的例子,price
和 peak_earnings
对于 2010-01-01
可以像这样单独计算:
SELECT price
FROM security_data
WHERE date = '2010-01-01'
AND security_id = 'SPX';
SELECT max(earnings) AS min_earnings
FROM bloomberg.security_data
WHERE date >= '2000-01-01'
AND date <= '2010-01-01'
AND security_id = 'SPX';
为了每行执行此操作,我使用了以下内容:
SELECT security_id, date, price
, CASE WHEN date1 >= min_date
THEN price / NULLIF(max(earnings) FILTER (WHERE date >= date1) OVER w, 0) END AS price_to_peak_earnings
FROM
(
SELECT record_id, security_id, price, date, earnings
, (date - interval '1 y')::date AS date1
, min(date) OVER (PARTITION BY security_id) AS min_date
FROM security_data
) d
WINDOW w AS (PARTITION BY security_id);
我认为这里的问题源于对 FILTER
的使用,因为它似乎没有像我想要的那样工作。请注意,在链接的 SQL Fiddle 中,我显示了 FILTER
的结果, 对于每一行 peak_earnings
和 minimum_earnings
只是整个数据集的最大值和最小值。它们应该是 earnings
的最大/最小值从 1 年前到当前行。
这是怎么回事?我从 this 的答案中知道我不能简单地说的问题 FILTER (WHERE date >= date1 AND date <= current_row.date)
,那么我缺少解决方案吗?我不能使用窗口框架,因为在任何给定的时间范围内我都有不确定的行数,所以我不能只说 OVER (ROWS BETWEEN 365 PRECEDING AND CURRENT ROW)
.我可以使用框架和过滤器吗?这可能超过一年,然后过滤器可以捕获每个无效日期。我试过这个但没有成功。
最佳答案
Can I use a frame and a filter?
你可以。但两者都有限制:
FILTER
子句中的表达式仅查看它获取值的相应行。无法引用窗口函数为其计算值的行。所以我看不到根据那个行制定过滤器的方法,除非我们制作一个巨大、昂贵的交叉连接——同一行用于许多不同的计算。或者我们回到 LATERAL
子查询,可以引用父行。
另一方面,框架定义根本不允许变量。正如您引用的相关答案中所讨论的那样,它需要一个固定的数字:
这些限制使您的特定查询难以实现。现在这应该是正确的:
SELECT *
FROM (
SELECT record_id, security_id, date, price
, CASE WHEN do_calc THEN max(earnings) OVER w1 END AS peak_earnings
, CASE WHEN do_calc THEN min(earnings) OVER w1 END AS minimum_earnings
, CASE WHEN do_calc THEN price / NULLIF(max(earnings) OVER w1, 0) END AS price_to_peak_earnings
, CASE WHEN do_calc THEN price / NULLIF(min(earnings) OVER w1, 0) END AS price_to_minimum_earnings
FROM (
SELECT *, (date - 365) >= min_date AND s.record_id IS NOT NULL AS do_calc
FROM (
SELECT security_id, min_date
, generate_series(min_date, max_date, interval '1 day')::date AS date
FROM (
SELECT security_id, min(date) AS min_date, max(date) AS max_date
FROM security_data
GROUP BY 1
) minmax
) d
LEFT JOIN security_data s USING (security_id, date)
) sub1
WINDOW w1 AS (PARTITION BY security_id ORDER BY date ROWS BETWEEN 365 PRECEDING AND 1 PRECEDING)
) sub2
WHERE record_id IS NOT NULL
ORDER BY 1, 2;
问题中没有任何内容表明每个 security_id
都会有同一天的行。在子查询 minmax
中计算每个 security_id
的最小/最大日期给我们最小时间范围。
计算的时间范围正好是行当前日期之前的 365 天,不包括当前行(ROWS BETWEEN 365 PRECEDING AND 1 PRECEDING
).从聚合中排除当前行以与当前行进行比较通常更有用。
我将计算条件调整为相同的时间范围以避免极端情况:(date - 365) >= min_date
在fiddle ,其中您为每个 1 月 1 日添加了 1 行,您可以看到闰年与固定数量的 365 天对比的效果。闰年(2001 年、2005 年...)后窗框为空。
我正在使用所有子查询,这通常比 CTE 快一点。
可以肯定的是,我们需要在框架定义中包含ORDER BY
。我相应地更新了您链接到的旧答案:
我使用 w1
作为窗口 name,时间为“1 年”。您可以添加 w2
等,并且每个天数可以是任意天数。如果需要,您毕竟可以适应闰年。甚至可能根据当前日期生成整个查询...
关于sql - 窗口函数过滤当前行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/48016525/
我试图通过这段代码读取未知数量的整数: while (1) { int c = getchar (); if (c == EOF) break;
我正试图找到一个类似于谷歌分析日期选择器的日期选择器: 知道 jQuery 是否提供了类似的东西吗? 最佳答案 这个 Twitter Bootstrap 风格的日期范围选择器非常接近。 https:/
我正在使用 javascript。如何获取当前 URL 的路径并将其分配给我的代码?这是我的代码: $(document).ready(function() { $(".share").hides
如何获得今天的Julian day number (JDN)相等的?或任何日期? 我看了又看,但只发现了一些产生“year-dayOfYear”的函数,而不是:2457854。 最佳答案 在 bash
我有相当简单的 UDP 服务器写在 c 上。 有时我需要知道在套接字中排队的所有 udp 数据包(字节)的当前长度。 据我了解,getsockopt 没有得到这样的信息。 欢迎使用 Linux 和 F
我一直在寻找几个小时来找到一个可以在图像中添加诸如“填充:5px”之类的东西的插件。每个人都通过纯 html 做到这一点吗?我们的客户需要一种方法来简单地使用按钮或右键单击上下文菜单来添加它。有什么建
是否有可能获得当前正在执行的 TCL 脚本的完整路径? 在 PHP 中,它将是:__FILE__ 最佳答案 根据“当前正在执行的 TCL 脚本”的含义,您实际上可能会寻找 info script ,甚
我最近从直接使用 ISession 转向了包装的 ISession,即工作单元类型模式。 我曾经使用 SQL Lite(内存中)对此进行测试。我有一个简单的帮助器类,它配置我的 SessionFact
我按照步骤操作 here在 WebStorm 中配置代码完成和其他内容,但我仍然收到以下语法错误。 我该如何解决这个问题? 最佳答案 通过相应地将“JavaScript 语言版本”(Settings/
我可以为我团队的 TFS 当前 Sprint 任务板添加书签吗?我们有两周的冲刺,因此 URL 每两周更改一次。 默认 URL 的形式为: http://[Server]/tfs/[Project]/
是否有 Subversion 命令可以显示当前版本号? 在svn checkout之后,我想启动一个脚本并需要变量中的修订号。如果有像 svn info get_revision_number 这样的
我正在编写表单的一个组件 首次安装组件时,sources={{}} ,一本空字典。由于该组件包装了现有的 Javascript 库,因此我正在实现一个自定义比较函数。为了让这个 diffing 函数
无论系统时间设置为多少以及机器所在的时区,我都需要正确的 UTC 时间。 (即使我必须打电话到互联网才能同步......) 是否有一些库或其他方法可以优雅地做到这一点? 最佳答案 如果您想获得准确可靠
我一边编码,一边拿出一些我和 friend 建立的旧网站来重新开始工作。我已经有一段时间没有做过任何 AJAX 了,当我试图找出我的代码失败的地方时,我发现没有显示很多资源。我猜这是因为我使用的是旧方
由于对性能的巨大影响,我从不怀疑我现在的桌面CPU是否有分支预测。当然可以。但各种 ARM 产品又如何呢? iPhone或Android手机有分支预测吗?较旧的任天堂 DS?基于 PowerPC 的
我有一个具有以下有效负载的 JWT: { "id": "394a71988caa6cc30601e43f5b6569d52cd7f6df", "jti": "394a71988caa6cc30
从其他一些帖子中,我能够通过以下方式获取当前 URI: 但是以下方法不起作用: 我很好奇为什么上面的方法不起作用,以及如何将当前 URI 分配给字符串。 最佳答案 每the javadocs ,g
我在表格 View 中有几个单元格。现在在任何给定的时间点,我想计算 View 中单元格的当前高度,即如果它是 View 的 3/4,它应该返回 (cellheight)*3/4 高度。 我通过以下方
这是网站的身份验证脚本。这安全吗?是最近的节目吗?它已经过时了吗?是否有“更好更安全的方法”我很新,但我没有看到太多地方使用 header 授权。 如有任何帮助,我们将不胜感激!这是我制作的第一个登录
我已经在其他 stackoverflow 线程上检查过这个错误,但在我的代码中没有发现任何错误。也许我累了,但我觉得还好。 网站.urls.py: from django.conf.urls impo
我是一名优秀的程序员,十分优秀!