求开始累计、年初到本期累计的SQL

最新推荐文章于 2024-03-21 17:26:19 发布

原创最新推荐文章于 2024-03-21 17:26:19 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #join #sql #table

05数据库专栏收录该内容

6 篇文章

订阅专栏

该博客探讨了如何在SQL中计算成本状态表(cs_f_CostStatus)中实际产值(actAmt)、计量(balAmt)和回款(recAmt)的年累计和开累计。给出了三种不同的SQL查询方法，包括一次性计算年累和开累，仅计算开累，以及仅计算年累。每种方法都通过内连接操作并根据特定条件进行求和，然后按costKPIKey、orgKey和dayKey进行分组。

CREATE TABLE cs_f_CostStatus(
[costKPIKey] [int] NULL,
[orgKey] [int] NULL,
[dayKey] [int] NULL,
[actAmt] [numeric](19, 6) NULL,
[balAmt] [numeric](19, 6) NULL,
[recAmt] [numeric](19, 6) NULL,
)
在以上表结构，求实际产值actAmt、计量balAmt、回款recAmt的年累、开累,其中dayKey以200904的数字形式表示年月。

现有以下SQL，评价性能等方面的优缺点.
--一次求年累开累
select cs1.costKPIKey, cs1.orgKey, cs1.dayKey, sum(cs2.actAmt) as actAmtSum,
sum(cs2.balAmt) as balAmtSum, sum(cs2.recAmt) as recAmtSum,
sum(CASE WHEN (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey) THEN cs2.actAmt ELSE 0 END) as actAmtSumYear,
sum(CASE WHEN (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey) THEN cs2.balAmt ELSE 0 END) as balAmtSumYear,
sum(CASE WHEN (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey) THEN cs2.recAmt ELSE 0 END) as recAmtSumYear
from cs_f_CostStatus cs1
inner join cs_f_CostStatus cs2
on (cs1.costKPIKey=cs2.costKPIKey) and (cs1.orgKey=cs2.orgKey) and (cs1.dayKey>=cs2.dayKey)
group by cs1.costKPIKey, cs1.orgKey, cs1.dayKey
order by cs1.costKPIKey, cs1.orgKey, cs1.dayKey

--只求开累
select cs1.costKPIKey, cs1.orgKey, cs1.dayKey, sum(cs2.actAmt) as actAmtSumYear,
sum(cs2.balAmt) as balAmtSumYear, sum(cs2.recAmt) as recAmtSumYear
from cs_f_CostStatus cs1
inner join cs_f_CostStatus cs2
on (cs1.costKPIKey=cs2.costKPIKey) and (cs1.orgKey=cs2.orgKey) and (cs1.dayKey>=cs2.dayKey)
group by cs1.costKPIKey, cs1.orgKey, cs1.dayKey

--只求年累
select cs1.costKPIKey, cs1.orgKey, cs1.dayKey, sum(cs2.actAmt) as actAmtSumYear,
sum(cs2.balAmt) as balAmtSumYear, sum(cs2.recAmt) as recAmtSumYear
from cs_f_CostStatus cs1
inner join cs_f_CostStatus cs2
on (cs1.costKPIKey=cs2.costKPIKey) and (cs1.orgKey=cs2.orgKey) and (cs1.dayKey>=cs2.dayKey)
and (cs1.dayKey-(cs1.dayKey % 100)<=cs2.dayKey)
group by cs1.costKPIKey, cs1.orgKey, cs1.dayKey