正文

重写查询以使用除 CTE 和子查询之外的窗口函数

 2023-03-31  37

关键词：

【中文标题】重写查询以使用除 CTE 和子查询之外的窗口函数【英文标题】：Rewrite query to use window functions except CTE's and subqueries 【发布时间】：2018-01-22 06:24:46 【问题描述】：

我有 2 个相关的表。

package:

| id_sort | volume | date |
---------------------------
| int     | float  | date |

sort:

| id  | id_standard |
---------------------
| int | int         |

我需要计算某种类型的音量占sort.id_standard 过滤的总音量的百分比。有 CTE 的完整查询

WITH total(volume) AS (
    SELECT SUM(package.volume) as volume
    FROM package
    LEFT JOIN sort ON sort.id = package.id_sort
    WHERE
        sort.id_standard IN (2,3)
        AND package.date >= CONVERT(VARCHAR(7), GETDATE(), 120) + '-01 08:00:00' -- Start of current month
        AND package.id_conv <> 12 -- additional filter
), filtered(volume) AS (
    SELECT SUM(package.volume) as volume
    FROM package
    WHERE package.id_sort = 17
        AND package.date >= CONVERT(VARCHAR(7), GETDATE(), 120) + '-01 08:00:00' -- start of current month
        AND package.id_conv <> 12 -- additional filter
)
SELECT CAST((filtered.volume * 100 / total.volume) AS NUMERIC(3,2)) [percentage] FROM total, filtered;

我敢肯定，窗口函数在那里会做得最好，但在现实生活中没有使用它们的经验。

【问题讨论】：

您如何确定窗口函数在此处工作得最好？如果您使用窗口函数，结果中将返回更多行并且可能不正确。 【参考方案1】：

这里不需要使用window 函数，这样的东西应该会有所帮助

SELECT [percentage] = Cast(( volume * 100 / Nullif(volume,0) ) AS NUMERIC(3, 2))
FROM   (SELECT total = Sum(volume),
               volume = Sum(CASE WHEN p.id_sort = 17 THEN p.volume ELSE 0 END)
        FROM   package p
               LEFT JOIN sort s
                      ON s.id = p.id_sort
                         AND sort.id_standard IN ( 2, 3 )
        WHERE  p.date >= CONVERT(VARCHAR(7), Getdate(), 120)
                         + '-01 08:00:00' -- Start of current month
               AND p.id_conv <> 12)a

【讨论】：

需要将 id_standard 的过滤器替换为连接条件中的常见条件（有 LEFT JOIN，所以我确实有错误的结果集没有被该条件过滤）

css 媒体查询以针对除 IE 之外的所有浏览器

】css媒体查询以针对除IE之外的所有浏览器【英文标题】：cssmediaquerytotargetallbrowsersexceptforIE【发布时间】：2014-09-2904:08:36【问题描述】：我正在寻找一个针对除IE之外的所有浏览器的媒体查询。例如：@media(........)有没有办法做到... 查看详情

调整查询以使用 CTE

】调整查询以使用CTE【英文标题】：AdaptingquerytouseCTE【发布时间】：2019-01-3102:02:24【问题描述】：我正在寻找在查询中使用CTE，因为我得到了相同的选择，但我以前从未使用过CTE，有人可以帮助我吗？我需要firstTable可以在其他... 查看详情

如何构建子查询/CTE 以替换嵌套聚合函数？

】如何构建子查询/CTE以替换嵌套聚合函数？【英文标题】：Howtostructuresubqueries/CTEstoreplacenestedaggregatefunctions?【发布时间】：2019-07-2916:21:15【问题描述】：我试图找出“amt”值的百分比属于每个“百分比”存储桶。示例数据：amt|p... 查看详情

使用函数的非常慢的 SQL Server 查询

...eClause根据where子句，查询运行速度会非常缓慢。我使用CTE重写了该查询并很好地避免了该问题，但从未找到答案。最近对另一个类似的查询进行了调整以查看详情

需要 SQL 查询以获取除 sysadmin 程序之外的所有并发程序的列表

】需要SQL查询以获取除sysadmin程序之外的所有并发程序的列表【英文标题】：NeedSQLquerytogetlistofallconcurrentprogramotherthansysadminprogram【发布时间】：2020-12-2207:27:01【问题描述】：我正在尝试生成除OracleEBSR12.1中的sysadmin程序之外的所... 查看详情

带有子查询的 CTE 查询在小型索引表上很慢；如何在 MySQL 上进行优化？

...：2020-11-1316:05:56【问题描述】：我最近将一个复杂的查询重写为带有子查询的CTE查询，部分原因是为了了解有关窗口函数的更多信息。新的很慢；对于只有几千行的索引表，在快速硬件上大约需要0. 查看详情

具有 CTE 的 T-SQL 窗口函数，使用先前计算的值

...时间】：2021-11-1320:12:03【问题描述】：我尝试使用以下CTE查询计算Result字段中引用先前Result值的值：WITHcteA(N,val1)AS(SELECT1,5.1UNIONSELECT2,6.5UNIONSELECT3,7.5UNI 查看详情

PostgREST 在子查询或 CTE 中使用限制和偏移量

】PostgREST在子查询或CTE中使用限制和偏移量【英文标题】：PostgRESTusinglimitandoffsetinsubqueriesorCTE【发布时间】：2018-05-2921:35:48【问题描述】：我们在项目中使用PostgREST来处理一些相当复杂的数据库视图。从某个时候开始，当我们使... 查看详情

djangoorm：带后续过滤的窗口函数(代码片段)

...响窗口）。有一个提示，就是要把window函数包在一个内部查询中，所以最后的SQL是这样的（据我了解）。SELECT*FROM(SELECT*,*window_function*FROMTABLE)WHERE*filtering_conditions*问题是：如何用DjangoORM写这个查询？答案另一种解决方案是通用表... 查看详情

无法计算 CTE 子查询输出之间的差异以用于更大的 PostgreSQL 查询输出列

】无法计算CTE子查询输出之间的差异以用于更大的PostgreSQL查询输出列【英文标题】：UnabletocalculatedifferencebetweenCTEsubqueryoutputsforuseinlargerPostgreSQLqueryoutputcolumn【发布时间】：2016-10-2912:24:43【问题描述】：在Shell中使用PostgreSQLv9.4.5... 查看详情

如何使用 MySQL 查询从表中选择除一列之外的所有内容？ [复制]

】如何使用MySQL查询从表中选择除一列之外的所有内容？[复制]【英文标题】：HowtoselectallfromtableexceptonecolumnusingMySQLquery?[duplicate]【发布时间】：2020-09-1717:27:06【问题描述】：我想使用MySQL从表中选择除一列之外的所有列。我知道... 查看详情

如何在另一个查询中使用 CTE 查询的结果

】如何在另一个查询中使用CTE查询的结果【英文标题】：HowtousetheresultsofaCTEqueryinsideanotherquery【发布时间】：2022-01-0711:31:42【问题描述】：我想获取CTE查询的结果并在另一个查询中使用它。此简化示例使用CTE查询返回id列表。witht... 查看详情

如何重写“选择顶部 x 子查询”以使用连接？

】如何重写“选择顶部x子查询”以使用连接？【英文标题】：Howtorewritea\'selecttopxsubquery\'tousejoins?【发布时间】：2016-01-1823:14:10【问题描述】：我有一个使用子查询的查询，我想重写为一个连接，以获得更好的性能。（毫秒访问... 查看详情

为啥我们需要除查询之外的任何类型的接线？

】为啥我们需要除查询之外的任何类型的接线？【英文标题】：Whywouldweneedanytypewiringsotherthanthoseforqueries?为什么我们需要除查询之外的任何类型的接线？【发布时间】：2021-09-2802:55:55【问题描述】：在GraphQLJava实现的document中，类... 查看详情

在 SQL 查询中选择除最大记录之外的所有记录

】在SQL查询中选择除最大记录之外的所有记录【英文标题】：selectallbutmaxrecordinSQLquery【发布时间】：2013-03-0617:05:16【问题描述】：我一直在努力让它发挥作用，但我无处可去。我需要的是以下内容：我需要能够为特定表选择除MA... 查看详情

使用 group by 子句和子查询的 postgresql 查询

】使用groupby子句和子查询的postgresql查询【英文标题】：postgressqlqueryusinggroupbyclauseandsubquery【发布时间】：2016-05-0905:38:12【问题描述】：您好，我有一张包含以下信息的表格。agent(agent_id,salary,city,country)我正在尝试进行查询以满... 查看详情

MySQL - 查询除第一行之外的所有行[重复]

】MySQL-查询除第一行之外的所有行[重复]【英文标题】：MySQL-Queryforallrowsexceptfirstone[duplicate]【发布时间】：2016-10-1011:20:34【问题描述】：我一直无法获得类似查询的结果-我需要表中除第一行之外的所有行，因为我只有第一行有... 查看详情

正文