When to Use Common Table Expression (Cte)

When to use Common Table Expression (CTE)

One example, if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.

An example of self referencing is recursion: Recursive Queries Using CTE

For exciting Microsoft definitions
Taken from Books Online:

A CTE can be used to:

• Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.

• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.

• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.

• Reference the resulting table multiple times in the same statement.

How to use cte?

You need to use this format

;WITH Base_cte AS 
    (
        SELECT
        FROM
        WHERE
    )
,CTE1 AS
(
       SELECT
       FROM Base_cte
       WHERE
)
SELECT * FROM CTE1

CTE (Common Table Expression) vs Temp tables or Table variables, which is faster?

We got a 50% increase in speed moving to CTE in one particular case so it's worth giving it a go but any performance related enhancements need to be bench marked so you can compare one against another.

PS: we wrote more than one query with a CTE in it before we got the one we now use.

Common Table Expression [CTE] and INNER JOIN

The CTEs only yield one column named Premier_trimestre or Deuxieme_trimestre.

If you want to include the commune column from import.dvf1 then you need to include it in the SELECT list of the CTEs.

You are also creating a cross join of the table with itself with this part FROM import.dvf1, (SELECT commune FROM import.dvf1) which is most probably the reason why the query takes forever.

I think what you intended is:

WITH P1 as  
(
  SELECT dvf1.commune, -- this "exposes" the commune column
         count(*) as Premier_trimestre
  FROM import.dvf1
  WHERE dvf1.date_mutation BETWEEN '2020-01-01' AND '2020-03-31' 
  GROUP BY dvf1.commune
)
, P2 as 
(
  SELECT dvf1.commune,  
         count(*) as Deuxieme_trimestre
  FROM import.dvf1
  WHERE dvf1.date_mutation BETWEEN '2020-04-01' AND '2020-06-30' 
  GROUP BY dvf1.commune
)
SELECT * 
FROM P1     
  JOIN P2 ON p1.commune = p2.commune

However, you can simplify this substantially by using filtered
aggregation:

SELECT commune, 
       count(*) filter (where extract(month from dvf1.date_mutation) in (1,2,3)) as Premier_trimestre, 
       count(*) filter (where extract(month from dvf1.date_mutation) in (4,5,6)) as Deuxieme_trimestre
FROM import.dvf1 
WHERE dvf1.date_mutation BETWEEN '2020-01-01' AND '2020-06-30'  -- this covers both ranges
GROUP dvf1.commune

Update using Common Table Expression

As documented, the UPDATE cannot yet see the rows inserted in the same statement.

The trick is to calculate the sum first, right when you insert the row into testsales.

---

Related Topics