CTE and Cursor in SQL

Scenarios to use CTEs: Best for scenarios where you need to simplify complex queries, perform recursive operations, or break down a query into logical parts. They are particularly useful for read-only operations and when you need to reference the same result set multiple times within a query.

Scenarios to use Cursors: Best for scenarios where you need to perform operations on each row individually, such as batch processing or when complex procedural logic is required for each row. However, cursors can be slower and less efficient than set-based operations, so they should be used sparingly and only when necessary.

Data: 1. To Create a Table.

CREATE TABLE sales_history (

  order_id INT,

  sales_date DATE,

  salesperson VARCHAR(20),

  order_value INT

);

2.To insert the values to the table created.

INSERT INTO sales_history

(order_id, sales_date, salesperson, order_value) VALUES

(1, '20210801', 'John', 15),

(2, '20210801', 'Sarah', 8),

(3, '20210801', 'Sally', 19),

(4, '20210802', 'John', 2),

(5, '20210802', 'Mark', 18),

(6, '20210803', 'Sally', 3),

(7, '20210803', 'Mark', 21),

(8, '20210803', 'Sarah', 16),

(9, '20210804', 'John', 4);

CTE:Common Table Expression (CTE) in SQL is a temporary result set that you can refer with SELECT, INSERT, UPDATE, or DELETE statement. It's like creating a temporary table that exists only for the duration of the query execution.

CTEs are useful for making complex queries more readable and manageable by breaking them down into smaller, more understandable parts. Its like a mini-tables that you create on-the-fly to simplify your SQL queries.

For example, if you need to perform multiple calculations or joins in a query, you can use a CTE to calculate intermediate results and then refer those results in the main query, making the overall query easier to understand and maintain.

Sample using CTE: To find total Rolling sum of order value for each day using CTE.

WITH Runningtotal AS (

    SELECT

        sales_date,

        order_value,

        SUM(order_value) OVER (ORDER BY sales_date) AS cumulative_sales

    FROM

        sales_history

)

SELECT

    sales_date,

    order_value,

    cumulative_sales

FROM

    Runningtotal;

Query Brief: In the above query WITH clause define CTE name as "Runningtotal and Rolling sum of order value for each day is calculated inside it. It aggregates total sum of 'Order_Value' field as cummulative field from 'sales_history' table and orders them in ascending order of 'sales_date'. It displays 'Sales_date', 'Order_value' Cumulative_sales' from 'Runningtotal' CTE.

The output: Row 1 calculates total sum of order of 'order_value' on same 'Sales_date' hence 'Running _total' = 15(current row)+8+19. Row 2 calculates total sum of order of 'order_value'  on same 'Sales_date' hence 'Running _total' = 15+8(current row)+19.Row 3 calculates total sum of order of 'order_value'  on same 'Sales_date' hence 'Running _total' = 15+8+19(Current value).Row 4 calculates total sum of order of 'order_value'  on same 'Sales_date' hence 'Running _total' = 42(previous day sale)+2(current row)+18. Running total is reset based on 'sales_date' Partition..

Query run time is 107.

Cursor: Cursors are typically used within stored procedures or functions.

Let imagine a table with multiple rows of data, and we have to perform operation on each row individually, like updating or deleting specific rows based on certain conditions. Cursors provide a way to iterate through the rows of the result set, allowing you to process each row sequentially. Cursors are useful when you need to perform row-level operations, but they can also be less efficient than set-based operations and should be used judiciously, especially with large datasets.

Cursor Query components:

• Declaration: Declare a cursor and associate it with a SELECT statement that retrieves the data you want to work with. Variables are defined here.

• Opening: open the cursor, which executes the SELECT statement and creates a temporary result set.

• Fetching: fetch rows from the result set one at a time, typically using a loop.

• Performing: perform operations on the current row of data (Rolling Calculation)

• Closing: After processing all the rows, close the cursor to release the associated resources.
  

Sample using Cursor: To find total Rolling sum of order value for each day using cursor.

CREATE OR REPLACE FUNCTION CalculateRollingCumulative()

RETURNS TABLE (

    sales_date DATE,

    order_value DECIMAL(18, 2),

    rolling_cumulative DECIMAL(18, 2)

)

AS $$

DECLARE

    v_SalesDate DATE;

    v_OrderValue DECIMAL(18, 2);

    v_RollingCumulative DECIMAL(18, 2) := 0;

BEGIN

    FOR v_SalesDate, v_OrderValue IN

        SELECT sh.sales_date, sh.order_value

        FROM sales_history sh

        ORDER BY sh.sales_date

    LOOP

        v_RollingCumulative := v_RollingCumulative + v_OrderValue;

        -- Return result row

        sales_date := v_SalesDate;

        order_value := v_OrderValue;

        rolling_cumulative := v_RollingCumulative;

        RETURN NEXT;

    END LOOP;

    RETURN;

END;

$$ LANGUAGE plpgsql;

Query Brief: In the above query, creates a new function or replaces an existing function name 'CalculateRollingCumulative()'. RETURNS TABLE Function returns table with field 'sales_date',

    'order_value',  'rolling_cumulative' .

Declaration: Declared variables are 'v_SalesDate DATE' is a variable to store the date of each sale. 'v_OrderValue DECIMAL(18, 2)' is a variable to store the value of each order. 'v_RollingCumulative DECIMAL(18, 2) := 0' is a variable to keep the cumulative sum of order values, initialized to 0.

Opening: Begin is the start of the executable function.

Fetching: Sub query orders the 'sh.sales_date', 'sh.order_value' as per ascending order of 'sh.sales_date' from 'sales_history sh' table.

Performing:  'v_RollingCumulative' updates the rolling cumulative sum by adding the current order value to the cumulative total. 'sales_date' assigns its value to 'v_SalesDate', 'order_value' assigns its value to ' v_OrderValue', 'rolling_cumulative' assigns its value to 'v_RollingCumulative' . 'RETURN NEXT' function returns the result of current and this loop runs until the last row.

Closing: 'RETURN' function returns the final result and 'END' function ends the cursor function.

'LANGUAGE plpgsql' Specifies that the function is written in PL/pgSQL, PostgreSQL's procedural language.

The output: Query has been run successfully in 82milliseconds.

To display the result for the above cursor function

SELECT * FROM CalculateRollingCumulative();

Row 1 calculates total sum of order of 'order_value' hence 'Running _total' = 15. Row 2 calculates totalsum of order of 'order_value' hence 'Running _total' = 15+8(current row).Row 3 calculates total sum of order of 'order_value' hence 'Running _total' = 15+8+19(Current value).Row 4 calculates total sum of order of 'order_value' hence 'Running _total' = 15+8+19+2(current row).

Query run time is 150.

Cursors can be used only when row-by-row processing is explicitly needed and cannot be efficiently achieved through set-based operations.