MAX TO_DATE OVER一个IF语句的两个ID

Question

我正在使用这个查询来连接amazon红移。

我有以下查询：

Select b.*, c."releasedate",
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), 'DD/Mon/YY') DESC) AS "rank_sprint",
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, 'YYYY-MM-DD') DESC) AS "rank_release",
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as "rank_issue",
MAX(IF (b.issueorigin='completed') AND (b.changeto='In Progress') and (b.changefield='status')
max(TO_DATE(SUBSTRING(b.changecreation,0,10),'YYYY-MM-DD')) OVER(b.issueid,b.sprintid)
) OVER (b.issueid,b.sprintid) as "lastinprogress"
from digitalplatforms.issues_braze b
Left join jira.releases c
On b.version_id=c.versionid

并输出以下错误：

[Amazon](500310) Invalid operation: syntax error at or near "max" 
Position: 459;

如果我只查询：

Select b.*, c.“releasedate”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(SUBSTRING(b.sprintenddate,0,9), ‘DD/Mon/YY’) DESC) AS “rank_sprint”,
DENSE_RANK() OVER(PARTITION BY b.originboardid ORDER BY TO_DATE(c.releasedate, ‘YYYY-MM-DD’) DESC) AS “rank_release”,
RANK() OVER (ORDER BY b.issueid, b.sprintid DESC) as “rank_issue”
from digitalplatforms.issues_braze b
Left join jira.releases c
On b.version_id=c.versionid

它是有效的。

有人能帮忙吗？

谢谢

Answer 1

没有"如果"；语句。SQL不是过程性的。您需要使用"；CASE"；或"；DECODE"；声明。

此外，您不能嵌套窗口函数。如果您的逻辑需要这样做，那么这些需要在查询的不同级别(SELECT级别(进行操作。然而，你确定这两个都需要窗口函数吗？他们使用相同的OVER子句，所以我预计不会。

只是根据你的查询进行猜测，但这能满足你的需求吗？

MAX(DECODE((b.issueorigin='completed') AND (b.changeto='In Progress') and (b.changefield='status')), true,
TO_DATE(SUBSTRING(b.changecreation,0,10),'YYYY-MM-DD')
) OVER (b.issueid,b.sprintid) as "lastinprogress"