Regla de Jira Automation cuando se fusiona una solicitud de incorporación de cambios
Regla de Jira Automation cuando se fusiona una solicitud de incorporación de cambios
Más allá de la metodología ágil
A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query. Much like a derived table, the result of a CTE is not stored and exists only for the duration of the query. This article will focus on non-recurrsive CTEs.
Más allá de la metodología ágil
CTEs, like database views and derived tables, enable users to more easily write and maintain complex queries via increased readability and simplification. This reduction in complexity is achieved by deconstructing ordinarily complex queries into simple blocks to be used, and reused if necessary, in rewriting the query. Example use cases include:
- Needing to reference a derived table multiple times in a single query
- An alternative to creating a view in the database
- Performing the same calculation multiple times over across multiple query components
Más allá de la metodología ágil
- Initiate a CTE using “WITH”
- Provide a name for the result soon-to-be defined query
- After assigning a name, follow with “AS”
- Specify column names (optional step)
- Define the query to produce the desired result set
- If multiple CTEs are required, initiate each subsequent expression with a comma and repeat steps 2-4.
- Reference the above-defined CTE(s) in a subsequent query
WITH
expression_name_1 AS
(CTE query definition 1)
[, expression_name_X AS
(CTE query definition X)
, etc ]
SELECT expression_A, expression_B, ...
FROM expression_name_1##Example CTE The following query answers the business question: “what is the average monthly cost per campaign for the company’s marketing efforts?” This could accomplished with a derived query (shown below); however, using a CTE improves readability
-- define CTE:
WITH Cost_by_Month AS
(SELECT campaign_id AS campaign,
TO_CHAR(created_date, 'YYYY-MM') AS month,
SUM(cost) AS monthly_cost
FROM marketing
WHERE created_date BETWEEN NOW() - INTERVAL '3 MONTH' AND NOW()
GROUP BY 1, 2
ORDER BY 1, 2)
-- use CTE in subsequent query:
SELECT campaign, avg(monthly_cost) as "Avg Monthly Cost"
FROM Cost_by_Month
GROUP BY campaign
ORDER BY campaignUsing a derived query:
SELECT campaign, avg(monthly_cost) as "Avg Monthly Cost"
FROM
-- this is where the derived query is used
(SELECT campaign_id AS campaign,
TO_CHAR(created_date, 'YYYY-MM') AS month,
SUM(cost) AS monthly_cost
FROM marketing
WHERE created_date BETWEEN NOW() - INTERVAL '3 MONTH' AND NOW()
GROUP BY 1, 2
ORDER BY 1, 2) as Cost_By_Month
GROUP BY campaign
ORDER BY campaign