我有一个api端点,它接受不同的参数来过滤特定的列。出于这个原因,我正在尝试构建一个查询,该查询可以很容易地向基本查询添加任意过滤器。出于某种原因,如果我使用:
SELECT "MY_VIEW".*
FROM "MY_VIEW"
-- Distinct on ID filter
WHERE ID IN (SELECT Max(ID)
FROM "MY_VIEW"
GROUP BY ID)
-- Other arbitrary filters...
ORDER BY "MY_VIEW"."NAME" DESC
我得到了糟糕的性能,所以我开始使用这个查询:
SELECT * FROM "MY_VIEW"
-- Distinct on ID filter
LEFT JOIN(
SELECT DISTINCT
FIRST_VALUE("MY_VIEW"."ID")
OVER(PARTITION BY "MY_VIEW"."UNIQUE_ID") as DISTINCT_ID
FROM "MY_VIEW"
) d ON d.DISTINCT_ID = "MY_VIEW"."ID"
-- Other arbitrary filters...
ORDER BY "MY_VIEW"."NAME" DESC
)
但是,当我离开join时,它会丢弃distinct过滤器。
另外,我不能使用rowid,因为它是一个视图。
该视图是一个版本化表。
索引信息
唯一性索引|连接状态索引| INDEX_TYPE | TEMPORARY |分区| JOIN_INDEX |列
NONUNIQUE | VALID | NORMAL |N| NO | NO | ID
UNIQUE |有效| NORMAL |N| NO | NO | UNIQUE_ID
NONUNIQUE |有效|域|N| NO | NO |名称
发布于 2019-06-26 04:57:58
我没有足够的名气来留下“评论”,所以我会把这篇文章作为“答案”发布。您的第一个示例是:
SELECT "MY_VIEW".*
FROM "MY_VIEW"
-- Distinct on ID filter
WHERE ID IN (SELECT Max(ID)
FROM "MY_VIEW"
GROUP BY ID)
-- Other arbitrary filters...
ORDER BY "MY_VIEW"."NAME" DESC
但是您是否意识到"GROUP BY ID“子句否定了MAX()函数对ID的影响?换句话说,您将获得所有行,并且将根据每行的ID计算最大值,并返回。。。该行的ID。也许可以尝试:
SELECT "MY_VIEW".*
FROM "MY_VIEW"
-- Distinct on ID filter
WHERE ID IN (SELECT Max(ID)
FROM "MY_VIEW")
-- Other arbitrary filters...
ORDER BY "MY_VIEW"."NAME" DESC
https://stackoverflow.com/questions/56759153
复制相似问题