文章來源地址http://www.zghlxwxcb.cn/news/detail-695052.html
1.?時區(qū)
1.1.?大航海時代伊始就在和時差打交道，而計算機時代的到來加劇了這一問題
1.2.?世界各地的人們都喜歡將太陽直射本地的時間作為正午
1.3.?無法強制所有人使用統(tǒng)一的時鐘
1.4.?世界被劃分為24個時區(qū)，同一時區(qū)內(nèi)的所有人都認同當前時間，而其他時區(qū)的人則不然
1.5.?為了確保有一個共同的計時參考點，十五世紀的航海家們將他們的時鐘設(shè)定為英國格林尼治時間，也就是后來所稱的格林尼治標準時（Greenwich mean time，GMT）
1.6.?協(xié)調(diào)世界時（coordinated universal time，UTC）
1.6.1.?GMT的一種變體
1.6.2.?原子鐘為基礎(chǔ)（或者更準確地說，是分布在全世界50個位置的200個原子鐘的平均時間，稱為“世界時”）
1.6.3.?SQL Server和MySQL都提供了可以返回當前的UTC時間戳的函數(shù)
1.6.3.1.?SQL Server的getutcdate()
1.6.3.2.?MySQL的utc_timestamp()
1.7.?大多數(shù)數(shù)據(jù)庫服務(wù)器根據(jù)當前所在地區(qū)設(shè)置默認時區(qū)，并提供工具以便在需要的時候修改時區(qū)
1.7.1.?用于存儲全球股票交易的數(shù)據(jù)庫通常會配置為使用UTC
1.7.2.?用于存儲特定零售企業(yè)銷售數(shù)據(jù)的數(shù)據(jù)庫則可能使用服務(wù)器所在時區(qū)
1.8.?MySQL提供兩種不同的時區(qū)設(shè)置
1.8.1.?全局時區(qū)
1.8.2.?會話時區(qū)
1.8.2.1.?對于每個登錄的用戶可能有所不同
1.8.2.2.?改變當前會話的時區(qū)設(shè)置
1.8.2.3.?mysql＞ SET time_zone = 'Europe/Zurich';
1.9.?Oracle Database用戶修改會話的時區(qū)設(shè)置
1.9.1.?ALTER SESSION TIMEZONE = 'Europe/Zurich'
2.?生成時間型數(shù)據(jù)
2.1.?從已有的date、datetime或time列復制數(shù)據(jù)
2.2.?執(zhí)行能夠返回date、datetime或time類型數(shù)據(jù)的內(nèi)建函數(shù)
2.3.?構(gòu)建可以被服務(wù)器評估的時間型數(shù)據(jù)的字符串表示
2.4.?日期格式的組成部分
2.4.1.?YYYY
2.4.1.1.?年份，包括世紀
2.4.1.2.?1000--9999
2.4.2.?MM
2.4.2.1.?月份
2.4.2.2.?01 (1月)~12 (12月)
2.4.3.?DD
2.4.3.1.?日
2.4.3.2.?01~31
2.4.4.?HH
2.4.4.1.?小時
2.4.4.2.?00~23
2.4.5.?HHH
2.4.5.1.?小時
2.4.5.2.?-838~838
2.4.6.?MI
2.4.6.1.?分鐘
2.4.6.2.?00~59
2.4.7.?SS
2.4.7.1.?分鐘
2.4.7.2.?00~59
2.5.?所需的日期組成部分
2.5.1.?date
2.5.1.1.?YYYYMM-DD
2.5.2.?datetime
2.5.2.1.?YYYY-MM-DD HH:MISS
2.5.3.?timestamp
2.5.3.1.?YYYY-MM-DD HH:MSS
2.5.4.?time
2.5.4.1.?HHH:M:SS
2.6.?cast()函數(shù)
2.6.1.?字符串到日期的轉(zhuǎn)換
2.6.1.1.?返回datetime類型的值
2.6.2.?mysql＞ SELECT CAST('2019-09-17 15:30:00' AS DATETIME);
2.6.3.?mysql
-＞ SELECT CAST('2019-09-17' AS DATE) date_field,
-＞ CAST('108:17:57' AS TIME) time_field;
2.7.?str_to_date()
2.7.1.?日期生成函數(shù)
2.7.2.?sql
UPDATE rental
SET return_date = STR_TO_DATE('September 17, 2019', '%M %d, %Y')
WHERE rental_id = 99999;
2.7.3.?第2個參數(shù)定義了日期字符串的格式
2.7.3.1.?%a 星期幾的簡寫，比如Sun、Mon、...
2.7.3.2.?%b 月份名稱的簡寫，比如Jan、Feb、...
2.7.3.3.?%c 月份的數(shù)字形式（0…12）
2.7.3.4.?%d 月份中的天數(shù)（00…31）
2.7.3.5.?%f 微秒數(shù)（000000…999999）
2.7.3.6.?%H 24小時制中的小時（00…23）
2.7.3.7.?%h 12小時制中的小時（01…12）
2.7.3.8.?%i 小時中的分鐘數(shù)（00…59）
2.7.3.9.?%j 一年中的天數(shù)（001…366）
2.7.3.10.?%M 月份的全稱（January…December）
2.7.3.11.?%m 月份的數(shù)值形式
2.7.3.12.?%p AM或PM
2.7.3.13.?%s 秒數(shù)（00…59）
2.7.3.14.?%W 星期幾的全稱（Sunday…Saturday）
2.7.3.15.?%w 一星期中的天數(shù)（0=周日；6=周六）
2.7.3.16.?%Y 4位數(shù)字表示的年份
2.7.4.?Oracle Database用戶可以使用to_date()函數(shù)，其用法與MySQL的str_to_date()函數(shù)相同
2.8.?訪問系統(tǒng)時鐘
2.8.1.?mysql＞ SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();
2.8.2.?Oracle Database提供了函數(shù)current_date()和current_timestamp()，但沒有提供current_time()
2.8.3.?Microsoft SQL Server只提供了current_timestamp()函數(shù)
2.9.?返回日期的時間型函數(shù)
2.9.1.?date_add()函數(shù)
2.9.1.1.?允許對指定日期添加各種間隔期（比如，日、月、年），以生成另一個日期
2.9.1.2.?mysql＞ SELECT DATE_ADD(CURRENT_DATE(), INTERVAL 5 DAY);
2.9.2.?常用的間隔類型
2.9.2.1.?second
2.9.2.1.1.?秒數(shù)
2.9.2.2.?minute
2.9.2.2.1.?分鐘數(shù)
2.9.2.3.?hour
2.9.2.3.1.?小時數(shù)
2.9.2.4.?day
2.9.2.4.1.?天數(shù)
2.9.2.5.?month
2.9.2.5.1.?月份
2.9.2.6.?year
2.9.2.6.1.?年數(shù)
2.9.2.7.?minute_second
2.9.2.7.1.?分鐘數(shù)和秒數(shù)，之間以”.”分隔
2.9.2.8.?hour_second
2.9.2.8.1.?小時數(shù)、分鐘數(shù)和秒效，之間以”.*分隔
2.9.2.9.?year_month
2.9.2.9.1.?分鐘數(shù)和秒數(shù)，之間以”.”分隔
2.9.3.?last_day()函數(shù)
2.9.3.1.?求得當前的月份并計算到月底所剩的天數(shù)
2.9.3.1.1.?會返回date類型
2.9.3.1.2.?底層邏輯很復雜，比如在要找出二月的最后一天時必須首先確定當前年份是否為閏年
2.9.3.2.?MySQL和Oracle Database都提供了
2.9.3.3.?SQL Server沒有提供與之功能接近的函數(shù)
2.9.3.4.?mysql＞ SELECT LAST_DAY('2019-09-17');
2.10.?返回字符串的時間型函數(shù)
2.10.1.?dayname()函數(shù)
2.10.1.1.?確定某一天是星期幾
2.10.1.2.?mysql＞ SELECT DAYNAME('2019-09-18');
2.10.2.?extract()函數(shù)
2.10.2.1.?SQL:2003標準的一部分
2.10.2.2.?Oracle Database中也同樣得到了實現(xiàn)
2.10.2.3.?記住一個函數(shù)的數(shù)種變體比記住一堆不同的函數(shù)更容易
2.10.2.4.?mysql＞ SELECT EXTRACT(YEAR FROM '2019-09-18 22:19:05');
2.10.2.5.?SQL Server沒有提供extract()函數(shù)的實現(xiàn)，但是提供了datepart()函數(shù)
2.10.2.5.1.?SELECT DATEPART(YEAR, GETDATE())
2.11.?返回數(shù)值的時間型函數(shù)
2.11.1.?datediff()
2.11.1.1.?返回兩個日期之間的天數(shù)
2.11.1.2.?mysql＞ SELECT DATEDIFF('2019-09-03', '2019-06-21');
2.11.1.3.?SQL Server也提供了datediff()函數(shù)，但比MySQL的實現(xiàn)更為靈活，可以為其指定間隔類型（年、月、日、小時等）
2.11.1.3.1.?SELECT DATEDIFF(DAY, '2019-06-21', '2019-09-03')
3.?轉(zhuǎn)換函數(shù)
3.1.?cast()函數(shù)
3.1.1.?SQL:2003標準
3.1.2.?MySQL、Oracle和Microsoft SQL Server中均已實現(xiàn)
3.1.3.?mysql＞ SELECT CAST('1456328' AS SIGNED INTEGER);
3.1.4.?cast()函數(shù)不接受格式化字符串
3.1.5.?如果待轉(zhuǎn)換的日期字符串并非默認格式（比如datetime類型的YYYY-MM-DD HH:MI:SS），需要先使用其他函數(shù)進行調(diào)整
4.?分析函數(shù)
4.1.?分析函數(shù)只能在SELECT子句中使用，所以如果需要根據(jù)分析函數(shù)的結(jié)果進行過濾或分組，通常需要使用嵌套查詢
4.2.?排名函數(shù)
4.2.1.?row_number
4.2.1.1.?為每一行返回一個唯一的排名，如果出現(xiàn)并列的情況，則任意分配排名
4.2.2.?rank
4.2.2.1.?在出現(xiàn)并列的情況下，返回相同的排名，會在排名中產(chǎn)生空隙
4.2.2.2.?在很多情況下，rank函數(shù)也許是最好的選擇
4.2.3.?dense_rank
4.2.3.1.?在出現(xiàn)并列的情況下，返回相同的排名，不會在排名中產(chǎn)生空隙
4.2.4.?mysql
-＞ SELECT customer_id, count(*) num_rentals,
-＞ row_number() over (order by count(*) desc) row_number_rnk,
-＞ rank() over (order by count(*) desc) rank_rnk,
-＞ dense_rank() over (order by count(*) desc) dense_rank_rnk
-＞ FROM rental
-＞ GROUP BY customer_id
-＞ ORDER BY 2 desc;
4.2.5.?生成多個排名
4.2.5.1.?通過在over子句中加入partition by子句來實現(xiàn)
4.2.5.2.?sql
SELECT customer_id, rental_month, num_rentals,
rank_rnk ranking
FROM
(SELECT customer_id,
monthname(rental_date) rental_month,
count(*) num_rentals,
rank() over (partition by monthname(rental_date)
order by count(*) desc) rank_rnk
FROM rental
GROUP BY customer_id, monthname(rental_date)
) cust_rankings
WHERE rank_rnk ＜= 5
ORDER BY rental_month, num_rentals desc, rank_rnk;
4.3.?報表函數(shù)
4.3.1.?找出離群值（outlier）
4.3.2.?生成整個數(shù)據(jù)集的匯總值/平均值
4.3.3.?可以使用聚合函數(shù)（min、max、avg、sum和count），但不是將其與group by子句并用，而是搭配over子句
4.3.4.?mysql
-＞ SELECT monthname(payment_date) payment_month,
-＞ amount,
-＞ sum(amount)
-＞ over (partition by monthname(payment_date)) monthly_total,
-＞ sum(amount) over () grand_total
-＞ FROM payment
-＞ WHERE amount ＞= 10
-＞ ORDER BY 1;
4.3.5.?mysql
-＞ SELECT monthname(payment_date) payment_month,
-＞ sum(amount) month_total,
-＞ round(sum(amount) / sum(sum(amount)) over ()
-＞ * 100, 2) pct_of_total
-＞ FROM payment
-＞ GROUP BY monthname(payment_date);
4.3.6.?mysql
-＞ SELECT monthname(payment_date) payment_month,
-＞ sum(amount) month_total,
-＞ CASE sum(amount)
-＞ WHEN max(sum(amount)) over () THEN 'Highest'
-＞ WHEN min(sum(amount)) over () THEN 'Lowest'
-＞ ELSE 'Middle'
-＞ END descriptor
-＞ FROM payment
-＞ GROUP BY monthname(payment_date);
4.3.7.?使用partition by子句來為分析函數(shù)定義數(shù)據(jù)窗口，允許按照公共值對行進行分組
4.3.8.?流水式總和
4.3.8.1.?mysql
-＞ SELECT yearweek(payment_date) payment_week,
-＞ sum(amount) week_total,
-＞ sum(sum(amount))
-＞ over (order by yearweek(payment_date)
-＞ rows unbounded preceding) rolling_sum
-＞ FROM payment
-＞ GROUP BY yearweek(payment_date)
-＞ ORDER BY 1;
4.3.9.?流水式平均值
4.3.9.1.?mysql
-＞ SELECT yearweek(payment_date) payment_week,
-＞ sum(amount) week_total,
-＞ avg(sum(amount))
-＞ over (order by yearweek(payment_date)
-＞ rows between 1 preceding and 1 following) rolling_3wk_avg
-＞ FROM payment
-＞ GROUP BY yearweek(payment_date)
-＞ ORDER BY 1;
4.3.10.?lag和lead
4.3.10.1.?涉及將一行中的值與另一行進行比較
4.3.10.2.?mysql
-＞ SELECT yearweek(payment_date) payment_week,
-＞ sum(amount) week_total,
-＞ lag(sum(amount), 1)
-＞ over (order by yearweek(payment_date)) prev_wk_tot,
-＞ lead(sum(amount), 1)
-＞ over (order by yearweek(payment_date)) next_wk_tot
-＞ FROM payment
-＞ GROUP BY yearweek(payment_date)
-＞ ORDER BY 1;
4.3.10.3.?mysql
-＞ SELECT yearweek(payment_date) payment_week,
-＞ sum(amount) week_total,
-＞ round((sum(amount) - lag(sum(amount), 1)
-＞ over (order by yearweek(payment_date)))
-＞ / lag(sum(amount), 1)
-＞ over (order by yearweek(payment_date))
-＞ * 100, 1) pct_diff
-＞ FROM payment
-＞ GROUP BY yearweek(payment_date)
-＞ ORDER BY 1;
5.?列值拼接
5.1.?處理數(shù)據(jù)窗口中的行組
5.2.?group_concat函數(shù)
5.2.1.?用于將一組列值轉(zhuǎn)換為單個分隔字符串
5.2.2.?一種將結(jié)果集反規(guī)范化（denormalize）以生成XML或JSON文檔的便捷方法
5.2.3.?mysql
-＞ SELECT f.title,
-＞ group_concat(a.last_name order by a.last_name
-＞ separator ', ') actors
-＞ FROM actor a
-＞ INNER JOIN film_actor fa
-＞ ON a.actor_id = fa.actor_id
-＞ INNER JOIN film f
-＞ ON fa.film_id = f.film_id
-＞ GROUP BY f.title
-＞ HAVING count(*) = 3;
5.2.4.?SQL Server，可以通過string_agg函數(shù)
5.2.5.?Oracle用戶則可以使用listagg函數(shù)
文章來源：http://www.zghlxwxcb.cn/news/detail-695052.html
到了這里，關(guān)于讀SQL學習指南（第3版）筆記12_時間函數(shù)和分析函數(shù)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
