sql - SAP HANA SQL 中是否有任何方法可以计算迭代中没有循环操作的百分比?
问题描述
我有一个如下的输入表,其中包含项目和金额列。并且还有 Low% (5%) 和 High% (50%) 常数值。
需要通过“总金额”得出“金额”的百分比,如果百分比小于低百分比或大于高百分比,则需要忽略这些记录并通过获取这些行的总数再次计算剩余行的百分比。
我可以在不使用 SAP HANA SQL 中的循环操作的情况下实现这一点吗?
解决方案
这对于大多数当前的 SQL RDBMS 都是可行的。该解决方案需要的是公用表表达式(CTE/“WITH”子句)和窗口函数。
鉴于这些,我们可以执行以下操作:
select current_time, * from m_database;
/*
CURRENT_TIME SYSTEM_ID DATABASE_NAME HOST START_TIME VERSION USAGE
12:17:48 PM HXE HXE hxehost 02/12/2019 12:12:06.815 PM 2.00.040.00.1553674765 DEVELOPMENT
*/
create column table item_amounts (item nvarchar(10) not null
, amount integer not null);
insert into item_amounts values ('A', 10 );
insert into item_amounts values ('A', 20 );
insert into item_amounts values ('A', 30);
insert into item_amounts values ('A', 40);
insert into item_amounts values ('A', 50);
insert into item_amounts values ('A', 60);
insert into item_amounts values ('A', 70);
insert into item_amounts values ('A', 80);
insert into item_amounts values ('A', 90);
insert into item_amounts values ('A', 100);
select * from item_amounts;
/*
ITEM AMOUNT
A 10
A 20
A 30
A 40
A 50
A 60
A 70
A 80
A 90
A 100
*/
-- first round: total per group
select
item
, amount
, SUM (amount) OVER (PARTITION BY item) as item_total_amount
from
item_amounts;
/*
ITEM AMOUNT ITEM_TOTAL_AMOUNT
A 10 550
A 20 550
A 30 550
A 40 550
A 50 550
A 60 550
A 70 550
A 80 550
A 90 550
A 100 550
*/
OP 的注意事项:通常以可执行形式提供测试数据是一个好主意,而不是作为屏幕截图或文本列表。像上面这样的事情会做。
第一步是执行简单的“组内总数百分比”计算。这是标准要求,并且易于使用窗口函数实现。
基于 OPs 示例,此实现将百分比四舍五入为完整的整数值。
select
item
, amount
, SUM (amount) OVER (PARTITION BY item) as item_total_amount
, round(100.0 * ( amount / SUM (amount) OVER (PARTITION BY item)), 0) as pct_of_item_total
from
item_amounts;
/*
ITEM AMOUNT ITEM_TOTAL_AMOUNT PCT_OF_ITEM_TOTAL
A 10 550 2
A 20 550 4
A 30 550 5
A 40 550 7
A 50 550 9
A 60 550 11
A 70 550 13
A 80 550 15
A 90 550 16
A 100 550 18
*/
现在,对于第二次“迭代”,我们应该过滤掉 PCT_OF_ITEM_TOTAL 介于 5 到 50 之间的项目。根据新的项目集,要求计算“组内总数的百分比”......这里,最迟,我们看到这又是完全相同的要求。
果然,我们可以用完全相同的代码来实现它。为此,我们将第一次迭代放入一个公用表表达式(stage
此处称为)中,并在基表中使用它进行第二次迭代:
with stage as (
select
item
, amount
, SUM (amount)
OVER (PARTITION BY item) as item_total_amount
, round(100.0 *
( amount / SUM (amount)
OVER (PARTITION BY item))
, 0) as pct_of_item_total
from
item_amounts)
select
s.item
, s.amount
, SUM (s.amount)
OVER (PARTITION BY s.item) as item_total_amount
, round(100.0 *
( s.amount / SUM (s.amount)
OVER (PARTITION BY s.item))
, 0) as pct_of_item_total
from
stage s
where
s.pct_of_item_total between 5 and 50;
/*
ITEM AMOUNT ITEM_TOTAL_AMOUNT PCT_OF_ITEM_TOTAL
A 30 520 6
A 40 520 8
A 50 520 10
A 60 520 12
A 70 520 13
A 80 520 15
A 90 520 17
A 100 520 19
*/
就是这样。
不需要循环,甚至没有使用 HANA 特定的功能。
推荐阅读
- javascript - 未能以 html 元素的形式接收 php 响应
- flutter - 如何从列表中删除特定项目?
- d3.js - 如何在d3中放置具有空投影的纬度和经度?
- powershell - PowerShell 一一捕获异常
- javascript - 检查div中是否存在某个单词
- c++ - 在没有上下文参数的情况下将有状态的 lambda 传递给 C 风格的函数
- javascript - Javascript:更改动态 api 数据的颜色?
- javascript - 谷歌优化 - 仅在没有发生重定向时执行 JS
- java - 线程“主”java.lang.IllegalArgumentException 中的异常:输入 == null!- 我相信eclipse找不到png文件
- sql-server - 加入额外表时的东西?