我有一個簡單的表格，其中包含不同日期的旅行。
| 行程編號 | 開始日期 | 結束日期 |
|---|---|---|
| 160320 | 2017-12-31 20:40:25 UTC | 2017-12-31 20:45:25 UTC |
| 160321 | 2018-01-12 21:01:51 UTC | 2018-01-12 22:01:51 UTC |
我只想創建一個顯示這些欄位的 SQL 查詢。
- 年
- 月
- trips_this_month，
- trips_previous_month
- difference_from_previous_month (count_this_month - count_previous_month)
- is_increased（是一個布爾列，如果我們看到增加則為真，否則為假）更新：我可以整理我的頭腦并撰寫一個簡單的查詢來獲取它們，但我仍然覺得我可以優化這個查詢。任何幫助將不勝感激。
SELECT
year,
month,
trips_this_month,
trips_previous_month,
case when difference_from_previous_month < 0 then false else true end as is_increased
FROM
(SELECT
year,
month,
number_of_trips AS trips_this_month,
LAG(number_of_trips,1,0) over (order by year,month) AS trips_previous_month,
number_of_trips - LAG(number_of_trips,1,0) OVER(order by year,month) AS difference_from_previous_month,
FROM(
SELECT EXTRACT(Month FROM start_date) AS month,
EXTRACT(Year FROM start_date) AS year,
COUNT(*) as number_of_trips
FROM a_table
group by month ,year
)
order by year, month
limit 100
)
但我忍不住要做得更多。我感謝進一步幫助完成它。
uj5u.com熱心網友回復：
試試這個：
WITH RECURSIVE cte AS (
SELECT MIN(start_date) minstdt, MAX(start_date) maxstdt FROM mytable
UNION ALL
SELECT minstdt INTERVAL 1 MONTH, maxstdt FROM cte
WHERE minstdt INTERVAL 1 MONTH <= maxstdt )
SELECT year,
month,
number_of_trips,
number_of_trips-IFNULL(prev_month_number_of_trips,0) AS This_month_vs_prev_month,
IF(number_of_trips > prev_month_number_of_trips,1,0) AS Is_increased
FROM
(SELECT
YEAR(cte.minstdt) AS year,
MONTH(cte.minstdt) AS month,
SUM(CASE WHEN start_date IS NULL THEN 0 ELSE 1 END) AS number_of_trips,
LAG(SUM(CASE WHEN start_date IS NULL THEN 0 ELSE 1 END))
OVER (ORDER BY YEAR(cte.minstdt), MONTH(cte.minstdt)) AS prev_month_number_of_trips
FROM cte
LEFT JOIN mytable
ON YEAR(cte.minstdt)=YEAR(start_date)
AND MONTH(cte.minstdt)=MONTH(start_date)
GROUP BY year, month) V
ORDER BY year, month;
- 我使用遞回公用表運算式 (
cte) 根據start_date表中出現的最小和最大日期生成日期。 - 我已經替換
EXTRACT()了YEAR()并且MONTH()使函式稍微短了一些。 - 我
LEFT JOIN將cte與資料表。
演示小提琴
看看你能不能用這個。
uj5u.com熱心網友回復：
考慮使用標準化的第一天日期的自聯接來比較當前和前幾個月的聚合：
WITH sub AS (
SELECT
DATE_SUB(
DATE_ADD(LAST_DAY(start_date), INTERVAL 1 DAY),
INTERVAL 1 MONTH
) AS month_year,
COUNT(*) AS number_of_trips
FROM a_table
GROUP BY month_year
), calc AS (
SELECT
YEAR(curr.month_year) AS year,
MONTH(curr.month_year) AS month,
COALESCE(curr.number_of_trips, 0) AS trips_this_month,
COALESCE(prev.number_of_trips, 0) AS trips_previous_month
FROM sub AS curr
LEFT JOIN sub AS prev
ON prev.month_year = DATE_SUB(curr.month_year, INTERVAL 1 MONTH)
)
SELECT
year,
month,
trips_this_month,
trips_previous_month,
trips_this_month - trips_previous_month AS difference_from_previous_month,
(trips_this_month - trips_previous_month) > 0 AS is_increased
FROM calc
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397701.html
上一篇：如何按日期運行多個分組的累計總數