{"id":101859,"date":"2024-04-08T18:57:06","date_gmt":"2024-04-08T18:57:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=101859"},"modified":"2026-03-16T10:59:50","modified_gmt":"2026-03-16T10:59:50","slug":"using-common-table-expressions-transforming-and-analyzing-data-in-postgresql-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/using-common-table-expressions-transforming-and-analyzing-data-in-postgresql-part-2\/","title":{"rendered":"Using Common Table Expressions: Transforming and Analyzing Data in PostgreSQL, Part 2"},"content":{"rendered":"

In the first article<\/a> in this transforming data series, I discussed how powerful PostgreSQL can be in ingesting and transforming data for analysis. Over the last few decades, this was traditionally done with a methodology called Extract-Transform-Load (ETL) which usually requires external tools. The goal of ETL is to do the transformation work outside of the database and only import the final form of data that is needed for further analysis and reporting.<\/p>\n

However, as databases have improved and matured, there are more capabilities to do much of the raw data transformation inside of the database. In doing so, we flip the process just slightly so that we Extract-Load-Transform (ELT), focusing on getting the raw data into the database and transforming it internally. In many circumstances this can dramatically improve the iteration of development because we can use SQL rather than external tools.<\/p>\n

While ELT won\u2019t be able to replace every transformation workload, understanding how to do the work can help improve many data transformation and analysis workloads.<\/p>\n

To demonstrate how SQL and PostgreSQL functions can be used to transform raw data directly in the database, in the first article I used sample data from the Advent of Code 2023, Day 7<\/a>. By the end of the first article, I had demonstrated how to take the sample input and transform it into a usable table of data that could be queried and analyzed. If you haven\u2019t read that article first, it\u2019s best to start there because you\u2019ll be able to load the sample data, understand the puzzle we are trying to solve, and some of the unique PostgreSQL features that improve the process.<\/p>\n

To get setup so that you can follow along, this simple script will create the \u2018dec07\u2019 table we need and insert a few rows of sample data. In the first article, I demonstrated two ways to do this that are more practical when dealing with raw input. This is just intended to get you started quickly.<\/p>\n

CREATE TABLE dec07 (\n\tid integer generated by default as identity,\n\tlines text\n);\nINSERT INTO dec07 (lines) VALUES \n\t('32T3K 765'),\n('T55J5 684'),\n('KK677 28'),\n('KTJJT 220'),\n('QQQJA 483');<\/pre>\n
By the end of the first article, we had this query.<\/p>\n
SELECT id AS hand, \nt.card, \nt.position, \nbid \nFROM dec07, \n     string_to_table(split_part(lines,' ',1),null) \n\t\tWITH ORDINALITY t(card,position),\n     split_part(lines,' ',2) bid;<\/pre>\n
Which, if you execute will return the following:<\/p>\n
hand|card|postition|bid|\n----+----+---------+---+\n   1|3   |        1|765|\n   1|2   |        2|765|\n   1|T   |        3|765|\n   1|3   |        4|765|\n   1|K   |        5|765|\n   2|T   |        1|684|\n   2|5   |        2|684|\n   2|5   |        3|684|\n   2|J   |        4|684|\n   2|5   |        5|684|\n   3|K   |        1|28 |\n   3|K   |        2|28 |\n   3|6   |        3|28 |\n   3|7   |        4|28 |\n   3|7   |        5|28 |\n   \u2026|\u2026   |        \u2026|\u2026  |<\/pre>\n
Finally, throughout that first article, all the examples that relied on multiple transformations had to use a derived table because we hadn\u2019t discussed Common Table Expressions (CTE) and their usefulness as a tool in SQL to breakup steps in the query and analysis process.<\/p>\n
In fact, the next part of solving the Day 7 puzzle involves converting the face value of each card into a point value. Without using a CTE, I can think of at least three ways to take the table above and add a new column with the point value of each card in the hand.<\/p>\n