[Fusion] How/Why to use PARTITION BY in Oracle SQL (GL BALANCE example)?

on


This post will show the scenario in which you can use PARTITION BY in Oracle SQL:

This example correlates to TRIAL BALANCE in Oracle Fusion. 

 

Benefits of using PARTITION BY

  • Data Segmentation: PARTITION BY allows you to divide your result set into partitions based on one or more columns. This can be particularly useful when you want to perform calculations on subsets of your data.
  • Independent Calculations: When you use PARTITION BY, the window function calculates results independently for each partition. This means that you can apply the same function across different groups of data without affecting each other's results.
  • Customized Aggregations: You can apply different aggregations or calculations within each partition. This allows for more customized analysis and reporting, as you can tailor calculations based on the characteristics of each partition.
  • Performance Optimization: In some cases, using PARTITION BY can lead to performance optimizations. By partitioning your data appropriately, you can reduce the amount of data that needs to be processed for each calculation, potentially improving query performance.
  • Clearer Queries: Using PARTITION BY can make your queries more concise and easier to understand, especially when performing complex analytical tasks. It helps to clearly define the scope of calculations within the query itself.

 

Suppose you have below query in which you are getting GL Balance for each account.

 

SELECT
     account
    ,amount
FROM (
    SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
) main

Result:


Here, the highlighted query is just a simple version of complex query in real scenario in which we are getting GL_BALANCE for each account. 

 

Now, suppose we want to find SUM of GL_BALANCE for each account. One way of doing this by repeating the same query in sub query and using it:

SELECT
     account
    ,amount
    ,(  SELECT
        sum(amount)
        FROM (
            SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
            SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
            SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
        ) q2
        WHERE q2.account=q1.account
    ) SUM_PER_ACCOUNT
FROM (
    SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
) q1

 Result:


 

Now, again if we also want to find the count of total rows for each account in that case we can write:

SELECT
     account
    ,amount
    ,(  SELECT
        sum(amount)
        FROM (
            SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
            SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
            SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
        ) q2
        WHERE q2.account=q1.account
    ) SUM_PER_ACCOUNT
    ,(  SELECT
        count(*)
        FROM (
            SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
            SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
            SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
            SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
        ) q2
        WHERE q2.account=q1.account
    ) COUNT_ACCOUNT
FROM (
    SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
) q1

Result:

This method seems to be okay but imagine the amount of processing it requires if highlighted query is too complex.

So, to achieve the same result you can use PARTITION BY as:

SELECT account
,amount
,SUM(amount) OVER (PARTITION BY account ORDER BY account) AS sum_per_account
,count(*) OVER (PARTITION BY account ORDER BY account) AS count_account
FROM (
    SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
    SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
    SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
) main;
 
 
Now, if you want to put WHERE condition on these columns sum_per_account and count_account, you can use below query:
 
SELECT account
,amount
,sum_per_account
FROM (
    SELECT account
    ,amount
    ,SUM(amount) OVER (PARTITION BY account ORDER BY account) AS sum_per_account
    FROM (
        SELECT '101' ACCOUNT, 100 AMOUNT FROM DUAL UNION
        SELECT '101' ACCOUNT, 200 AMOUNT FROM DUAL UNION
        SELECT '101' ACCOUNT, 300 AMOUNT FROM DUAL UNION
        SELECT '101' ACCOUNT, 400 AMOUNT FROM DUAL UNION
        SELECT '102' ACCOUNT, 100 AMOUNT FROM DUAL UNION
        SELECT '102' ACCOUNT, 200 AMOUNT FROM DUAL
    ) main
)
WHERE sum_per_account <>0;
 
 
 
 

Comments

Post a Comment