Skip to main content

CTE (Common Table Expressions) — SQL

Getting started: filtering your data

Let's start by examining an example payments table. Our goal is to roll up old months and put them into a summary table. To begin, we need to filter down to just older months:

SQL
SELECT 

  amount, 

  YEAR(payment_date), 

  MONTH(payment_date)

FROM 

  payments

WHERE 

  payment_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

Here, we're selecting the amount of the payment as well as the year and month that the payment was made. By limiting the results to payment_date values that are less than the first day of the current month, we ensure that we're only selecting data from previous months.

Grouping your data

Once we have our filtered data, we need to group it by year and month so we can roll it up into the summary table:

SQL
SELECT 

  sum(amount) as amount, 

  YEAR(payment_date) as `year`, 

  MONTH(payment_date) as `month`

FROM 

  payments

WHERE 

  payment_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

GROUP BY 

  `year`, `month`

This gives us a sum of the amount for each month that is in our historical data set.

Creating your summary table

Now it's time to create the summary table itself. We define the schema using the following statement:

SQL
CREATE TABLE payment_summary (

  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

  amount DECIMAL(9,2),

  `year` YEAR,

  `month` TINYINT UNSIGNED

);

We'll use this table to store our rolled-up historical data. Note that we've defined the year column to use the YEAR data type, which is typically not used since it only stores four-digit years. In this case it is actually useful!

Populating your summary table

To insert the data from our filtered query into the summary table, we can use the following statement:

SQL
INSERT INTO payment_summary (amount, year, month) SELECT 

  sum(amount) as amount, 

  YEAR(payment_date) as `year`, 

  MONTH(payment_date) as `month`

FROM 

  payments

WHERE 

  payment_date < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

GROUP BY 

  `year`, `month`

This will populate the summary table with the historic data we've rolled up.

Combining your summary table with live data

The last step is to incorporate this summary table with live data. We want to ensure that the summary table is up-to-date, but we also want to be able to query both historic and current data together. Here's one way we can do it:

SQL
SELECT 

  amount, 

  year, 

  month 

FROM 

  payment_summary




UNION ALL




SELECT 

  sum(amount) as amount, 

  YEAR(payment_date) as `year`, 

  MONTH(payment_date) as `month`

FROM 

  payments

WHERE 

  payment_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

This combines our rolled-up historical data with our current data. Note that we're using UNION ALL instead of UNION here, which preserves duplicate rows (of which there should be none in our case). We're using the same extract statement as before, but with the opposite condition to ensure that we're only selecting data that's been added this month.

Using a common table expression

Finally, we can use a common table expression (CTE) to treat this entire query as a single table:

SQL
WITH payment_data AS (

  SELECT 

    amount, 

    year, 

    month 

  FROM 

    payment_summary




  UNION ALL




  SELECT 

    sum(amount) as amount, 

    YEAR(payment_date) as `year`, 

    MONTH(payment_date) as `month`

  FROM 

    payments

  WHERE 

    payment_date >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')

)




SELECT * FROM payment_data

Here, we wrap our combined query in a WITH statement to define a new CTE called payment_data. We can then select from this CTE as if it were a real table.

Conclusion

By using a summary table, we can greatly reduce the amount of data processing required by our query. We've demonstrated how to filter and group large data sets, create a new summary table, and roll up data into it. We've also combined live and historical data using UNION and CTE statements. Taking the time to optimize your database in this way can result in faster queries and a more efficient system overall.

Comments

Post a Comment

Popular posts from this blog

MySQL's ACID compliance

Mysql acid compliance ACID is an acronym that stands for four key properties of database transactions: Atomicity Ensures that a transaction is treated as a single, indivisible unit of work Either all operations within a transaction are completed successfully, or none are If any part of the transaction fails, the entire transaction is rolled back to its previous state Prevents partial updates that could leave the database in an inconsistent state Consistency Guarantees that a transaction brings the database from one valid state to another valid state All data written to the database must adhere to defined rules, constraints, cascades, triggers, and other database integrity mechanisms Ensures that any transaction will not break the database's predefined rules Isolation Determines how and when changes made by one transaction become visible to other transactions Prevents interference between concurrent transactions MySQL provides different isolation levels: Read Uncommitted Read Commit...
1 comment
Read more

PHP OOPs exercise - Basic Oops

  Here are key PHP OOP (Object-Oriented Programming) exercise questions with solutions: Basic Class and Object Exercise: // Create a simple bank account class class BankAccount {     private $accountNumber;     private $balance;     public function __construct($accountNumber, $initialBalance = 0) {         $this->accountNumber = $accountNumber;         $this->balance = $initialBalance;     }     public function deposit($amount) {         if ($amount > 0) {             $this->balance += $amount;             return true;         }         return false;  ...
Post a Comment
Read more

Interview questions for Senior PHP Developer particle41.com

1.Self Introduction 2.Basic questions on session and cookie. 3.Where is session stored? 4.Difference between Cookie and session. 5.Will there be any session before session start? 6.Post Max execution time.How can we modify it? 7.We have a string, "BJFSJK".Without any php function reverse it with half the string length.   To reverse the string with half the string length without using any PHP functions, you can implement a simple algorithm to achieve the desired result. Here's how you can do it: Initialize two pointers, one at the beginning of the string and the other at the midpoint of the string. Swap characters between these two pointers iteratively, moving the pointers towards each other until they meet or cross each other. Here's the PHP code to implement this algorithm:  <?php $string = "ABC100"; $length = strlen($string); // Calculate the midpoint of the string $midpoint = (int)($length / 2); // Initialize pointers $start = 0; $end = $length - 1; //...
Post a Comment
Read more