sql - 在 HANA SQL 中使用窗口函数 ROW_NUMBER 分配组号
问题描述
我必须在这里输入很多文字才能发布。
请忽略此文本并在下面查看我的问题。
我有以下带有分区的代码:
SELECT rownum,"SRNUM" ,
"SRAnalyzeddate",
"Account",
"U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG",
"U_USD_TOTAL_UNDER_SUBSCRIBED",
"DV_U_BUSINESS_UNIT",
"CUSTOMER_SINCE",
"ContractEnd",
"Segment",
"Industry",
"Territory",
"Vertical",
"FIELD_GEO",
"FIELD_MAJOR_AREA",
"FIELD_AREA"
FROM
(
select SR."NUMBER" AS "SRNUM" ,SR."U_ANALYZED_DATE" AS "SRAnalyzeddate",SR."DV_SALES_ACCOUNT" AS "Account","U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG","U_USD_TOTAL_UNDER_SUBSCRIBED", "DV_U_BUSINESS_UNIT","CUSTOMER_SINCE",
acc."U_NEW_SEGMENT" AS "Segment",
acc."DV_INDUSTRY" As "Industry",
acc."DV_U_ACCOUNT_TERRITORY" As "Territory",
acc."DV_U_VERTICAL" As "Vertical",
ter."DV_U_GEO" AS "FIELD_GEO",
ter."DV_U_MAJOR_AREA" AS "FIELD_MAJOR_AREA",
ter."DV_U_AREA" AS "FIELD_AREA",
MAX("END_DATE") AS "ContractEnd",
ROW_NUMBER() OVER (PARTITION BY SR."NUMBER" ORDER BY SR."DV_SALES_ACCOUNT" DESC) AS rownum
from "SURF_RT"."SALES_ACCOUNT" acc
left join "SURF_RT"."SALES_REQUEST" SR on acc."NAME" = SR."DV_SALES_ACCOUNT"
left join "SURF_RT"."SALES_CONTRACT" con on con."DV_ACCOUNT"=SR."DV_SALES_ACCOUNT"
left join "SURF_RT"."U_SALES_TERRITORY" ter on acc."DV_U_ACCOUNT_TERRITORY" = ter."U_NAME"
JOIN "SURF_RT"."U_SALES_REQUEST_ALERT_LINES" line on sr."NUMBER"=line."DV_U_SALES_REQUEST"
JOIN "SURF_RT"."SALES_PRODUCT" prod on line."U_PRODUCT_CODE"=prod."U_PRODUCT_CODE"
WHERE
"DV_SALES_CATEGORY"='Compliance' AND SR."DV_STATE" NOT IN ('Closed Canceled')
AND YEAR("U_ANALYZED_DATE") = '2020' AND MONTH("U_ANALYZED_DATE") IN ('1','2','3','4')
AND acc."DV_TYPE" NOT IN ('Suspect','Prospect','Inactive','Inactive-Former Customer','Vendor')
GROUP BY SR."NUMBER" ,
SR."U_ANALYZED_DATE",
SR."DV_SALES_ACCOUNT",
"U_USD_TOTAL_POTENTIAL_NNACV",
"U_USD_TOTAL_UNDEPLOYED_BACKLOG",
"U_USD_TOTAL_UNDER_SUBSCRIBED",
"DV_U_BUSINESS_UNIT",
"CUSTOMER_SINCE",
acc."U_NEW_SEGMENT",
acc."DV_INDUSTRY",
acc."DV_U_ACCOUNT_TERRITORY",
acc."DV_U_VERTICAL",
ter."DV_U_GEO" ,
ter."DV_U_MAJOR_AREA" ,
ter."DV_U_AREA"
ORDER BY SR."DV_SALES_ACCOUNT","SRNUM",
"SRAnalyzeddate"
)
我希望单个 DV_SALES_ACCOUNT 的每个 SRNUM 具有不同的行号,如下所示:
SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM1 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM2 = rownum2
SRNUM3 = rownum3
SRNUM3 = rownum3
SRNUM3 = rownum3
相反,我得到了这个:
SRNUM1 = rownum1
SRNUM1 = rownum2
SRNUM1 = rownum3
SRNUM2 = rownum1
SRNUM2 = rownum2
SRNUM2 = rownum3
SRNUM3 = rownum1
SRNUM3 = rownum2
SRNUM3 = rownum3
我怎样才能解决这个问题?谢谢!
我在这里输入一个占位符,因为出于某种原因,他们认为我的问题是“代码太多”。
解决方案
您想要实现的目标的口头描述与您的预期输出不同。实际上,您当前获得的输出就是您所描述的。
回顾你的预期输出
SRNUM1 = rownum1 SRNUM1 = rownum1 SRNUM1 = rownum1 SRNUM2 = rownum2 SRNUM2 = rownum2 SRNUM2 = rownum2 SRNUM3 = rownum3 SRNUM3 = rownum3 SRNUM3 = rownum3
似乎您想为每个人分配SRNUM
自己的号码(每个帐户)。
有了这种洞察力,很容易回到你的 SQL 并实现它:
[...]
ROW_NUMBER() OVER
(PARTITION BY
SR."DV_SALES_ACCOUNT", SR."NUMBER"
) AS "ACCOUNT_SALES_REQUEST_NUMBER"
[...]
您要小心选择别名。ROWNUM
是一个已经在 SQL 中使用的术语,它将数据库技术问题与您的应用程序领域问题混合在一起。
举个例子,我"ACCOUNT_SALES_REQUEST_NUMBER"
在这里用过,打字时间很长,但至少它在罐子上说明了它是什么。
推荐阅读
- wordpress - Wordpress 插件 - 使用 AJAX 请求 WPDB
- node.js - 当我尝试从节点 API 获取数据时未定义
- php - Insert data into database if value doesn't exists in specific column
- python - 用于匹配 URL 中的电子邮件的正则表达式
- sql-server - 无法读取 Object.eval [as updateDirectives] 处未定义的属性“Nome”
- ansible - Ansible 动态清单:无法使用 group_vars
- c# - Visual Studio 2019 版本 c# 问题
- python - 用于 3d 数组列表的类似网格的函数
- php - Unable to show modal popup when LDAP authentication failed
- python - 转置后如何转置和合并相同的列名?