Function(arg1,..., argn) OVER ( [PARTITION BY <...>] [ORDER BY <....>] [
- The OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results
- PARTITION BY divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.
- ORDER BY clause Defines the logical order of the rows within each partition of the result set.
According to this SQL statement:
1: select FYR, --會計年度
2: ROC_YM, --年月
3: INCM_LOAN --融資財源收入
4: from FMS406VC
5: where fyr=102 and ROC_YM=10201;
We can get this result, and we would like to accumulate the value of INCM_LOAD:
We can use partition by clause to accumulate the value of INCM_LOAD
1: select FYR, --會計年度
2: ROC_YM, --年月
3: SUM(SUM(INCM_LOAN)) OVER (PARTITION BY FYR ORDER BY ROC_YM) AS CUM_INCM_LOAD --累積融資財源收入
4: from FMS406VC
5: where fyr=102 and ROC_YM=10201
6: GROUP BY FYR ,ROC_YM;
No comments:
Post a Comment