讀SQL學習指南（第3版）筆記12_時間函數和分析函數

来源:https://www.cnblogs.com/lying7/archive/2023/09/06/17678087.html
-Advertisement-
Play Games

![](https://img2023.cnblogs.com/blog/3076680/202309/3076680-20230904205926819-1818911722.png) # 1. 時區 ## 1.1. 大航海時代伊始就在和時差打交道，而電腦時代的到來加劇了這一問題 ## 1.2. ...


1. 時區

1.1. 大航海時代伊始就在和時差打交道，而電腦時代的到來加劇了這一問題

1.2. 世界各地的人們都喜歡將太陽直射本地的時間作為正午

1.3. 無法強制所有人使用統一的時鐘

1.4. 世界被劃分為24個時區，同一時區內的所有人都認同當前時間，而其他時區的人則不然

1.5. 為了確保有一個共同的計時參考點，十五世紀的航海家們將他們的時鐘設定為英國格林尼治時間，也就是後來所稱的格林尼治標準時（Greenwich mean time，GMT）

1.6. 協調世界時（coordinated universal time，UTC）

1.6.1. GMT的一種變體

1.6.2. 原子鐘為基礎（或者更準確地說，是分佈在全世界50個位置的200個原子鐘的平均時間，稱為“世界時”）

1.6.3. SQL Server和MySQL都提供了可以返回當前的UTC時間戳的函數

1.6.3.1. SQL Server的getutcdate()

1.6.3.2. MySQL的utc_timestamp()

1.7. 大多數資料庫伺服器根據當前所在地區設置預設時區，並提供工具以便在需要的時候修改時區

1.7.1. 用於存儲全球股票交易的資料庫通常會配置為使用UTC

1.7.2. 用於存儲特定零售企業銷售數據的資料庫則可能使用伺服器所在時區

1.8. MySQL提供兩種不同的時區設置

1.8.1. 全局時區

1.8.2. 會話時區

1.8.2.1. 對於每個登錄的用戶可能有所不同

1.8.2.2. 改變當前會話的時區設置

1.8.2.3. mysql＞ SET time_zone = 'Europe/Zurich';

1.9. Oracle Database用戶修改會話的時區設置

1.9.1. ALTER SESSION TIMEZONE = 'Europe/Zurich'

2. 生成時間型數據

2.1. 從已有的date、datetime或time列複製數據

2.2. 執行能夠返回date、datetime或time類型數據的內建函數

2.3. 構建可以被伺服器評估的時間型數據的字元串表示

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()函數

2.6.1. 字元串到日期的轉換

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. 日期生成函數

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個參數定義了日期字元串的格式

2.7.3.1. %a 星期幾的簡寫，比如Sun、Mon、...

2.7.3.2. %b 月份名稱的簡寫，比如Jan、Feb、...

2.7.3.3. %c 月份的數字形式（0…12）

2.7.3.4. %d 月份中的天數（00…31）

2.7.3.5. %f 微秒數（000000…999999）

2.7.3.6. %H 24小時制中的小時（00…23）

2.7.3.7. %h 12小時制中的小時（01…12）

2.7.3.8. %i 小時中的分鐘數（00…59）

2.7.3.9. %j 一年中的天數（001…366）

2.7.3.10. %M 月份的全稱（January…December）

2.7.3.11. %m 月份的數值形式

2.7.3.12. %p AM或PM

2.7.3.13. %s 秒數（00…59）

2.7.3.14. %W 星期幾的全稱（Sunday…Saturday）

2.7.3.15. %w 一星期中的天數（0=周日；6=周六）

2.7.3.16. %Y 4位數字表示的年份

2.7.4. Oracle Database用戶可以使用to_date()函數，其用法與MySQL的str_to_date()函數相同

2.8. 訪問系統時鐘

2.8.1. mysql＞ SELECT CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP();

2.8.2. Oracle Database提供了函數current_date()和current_timestamp()，但沒有提供current_time()

2.8.3. Microsoft SQL Server只提供了current_timestamp()函數

2.9. 返回日期的時間型函數

2.9.1. date_add()函數

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. 秒數

2.9.2.2. minute

2.9.2.2.1. 分鐘數

2.9.2.3. hour

2.9.2.3.1. 小時數

2.9.2.4. day

2.9.2.4.1. 天數

2.9.2.5. month

2.9.2.5.1. 月份

2.9.2.6. year

2.9.2.6.1. 年數

2.9.2.7. minute_second

2.9.2.7.1. 分鐘數和秒數，之間以”.”分隔

2.9.2.8. hour_second

2.9.2.8.1. 小時數、分鐘數和秒效，之間以”.*分隔

2.9.2.9. year_month

2.9.2.9.1. 分鐘數和秒數，之間以”.”分隔

2.9.3. last_day()函數

2.9.3.1. 求得當前的月份並計算到月底所剩的天數

2.9.3.1.1. 會返回date類型
2.9.3.1.2. 底層邏輯很複雜，比如在要找出二月的最後一天時必須首先確定當前年份是否為閏年

2.9.3.2. MySQL和Oracle Database都提供了

2.9.3.3. SQL Server沒有提供與之功能接近的函數

2.9.3.4. mysql＞ SELECT LAST_DAY('2019-09-17');

2.10. 返回字元串的時間型函數

2.10.1. dayname()函數

2.10.1.1. 確定某一天是星期幾

2.10.1.2. mysql＞ SELECT DAYNAME('2019-09-18');

2.10.2. extract()函數

2.10.2.1. SQL:2003標準的一部分

2.10.2.2. Oracle Database中也同樣得到了實現

2.10.2.3. 記住一個函數的數種變體比記住一堆不同的函數更容易

2.10.2.4. mysql＞ SELECT EXTRACT(YEAR FROM '2019-09-18 22:19:05');

2.10.2.5. SQL Server沒有提供extract()函數的實現，但是提供了datepart()函數

2.10.2.5.1. SELECT DATEPART(YEAR, GETDATE())

2.11. 返回數值的時間型函數

2.11.1. datediff()

2.11.1.1. 返回兩個日期之間的天數

2.11.1.2. mysql＞ SELECT DATEDIFF('2019-09-03', '2019-06-21');

2.11.1.3. SQL Server也提供了datediff()函數，但比MySQL的實現更為靈活，可以為其指定間隔類型（年、月、日、小時等）

2.11.1.3.1. SELECT DATEDIFF(DAY, '2019-06-21', '2019-09-03')

3. 轉換函數

3.1. cast()函數

3.1.1. SQL:2003標準

3.1.2. MySQL、Oracle和Microsoft SQL Server中均已實現

3.1.3. mysql＞ SELECT CAST('1456328' AS SIGNED INTEGER);

3.1.4. cast()函數不接受格式化字元串

3.1.5. 如果待轉換的日期字元串並非預設格式（比如datetime類型的YYYY-MM-DD HH:MI:SS），需要先使用其他函數進行調整

4. 分析函數

4.1. 分析函數只能在SELECT子句中使用，所以如果需要根據分析函數的結果進行過濾或分組，通常需要使用嵌套查詢

4.2. 排名函數

4.2.1. row_number

4.2.1.1. 為每一行返回一個唯一的排名，如果出現併列的情況，則任意分配排名

4.2.2. rank

4.2.2.1. 在出現併列的情況下，返回相同的排名，會在排名中產生空隙

4.2.2.2. 在很多情況下，rank函數也許是最好的選擇

4.2.3. dense_rank

4.2.3.1. 在出現併列的情況下，返回相同的排名，不會在排名中產生空隙

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子句來實現

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. 報表函數

4.3.1. 找出離群值（outlier）

4.3.2. 生成整個數據集的彙總值/平均值

4.3.3. 可以使用聚合函數（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子句來為分析函數定義數據視窗，允許按照公共值對行進行分組

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. 處理數據視窗中的行組

5.2. group_concat函數

5.2.1. 用於將一組列值轉換為單個分隔字元串

5.2.2. 一種將結果集反規範化（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函數

5.2.5. Oracle用戶則可以使用listagg函數


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Unity UGUI的Dropdown（下拉菜單）組件的介紹及使用
    # Unity UGUI的Dropdown（下拉菜單）組件的介紹及使用 ## 1. 什麼是Dropdown組件？ Dropdown（下拉菜單）是Unity UGUI中的一個常用組件，用於在用戶點擊或選擇時顯示一個下拉菜單，提供多個選項供用戶選擇。 ## 2. Dropdown組件的工作原理 Drop ...
  • 每個.NET開發都應掌握的C#集合知識點
    上篇文章講述了[C#委托和事件知識點](http://mp.weixin.qq.com/s?__biz=MzI2NDE1MDE1MQ==&mid=2650851553&idx=1&sn=b1c601eaf4bb4f2898cbd418fa0ae426&chksm=f1456509c632ec1f8f ...
  • WPF - 陰影效果
    基本屬性： 屬性 說明 描述 Color 設置陰影效果背景色 Color="Red" ShadowDepth 設置陰影的偏移度 ShadowDepth="5" Direction 設置陰影的角度 Direction="-45" BlurRadius 設置陰影模糊程度 BlurRadius="20" ...
  • Linux運維工程師面試題（7）
    [toc] # Linux運維工程師面試題（7） > 祝各位小伙伴們早日找到自己心儀的工作。 > 持續學習才不會被淘汰。 > 地球不爆炸，我們不放假。 > 機會總是留給有有準備的人的。 > 加油，打工人！ ## 1 常用的 ansible 模塊有哪些 - Ping - Command - Shell ...
  • 深入理解零拷貝技術
    1.禁用swap分區 swapoff -a && sysctl -w vm.swappiness=0 sed -ri '/^[^#]*swap/s@^@#@' /etc/fstab grep swap /etc/fstab 2. 禁用防火牆和selinux 2.1禁用防火牆，網路管理，郵箱 1 sy ...
  • 編譯Linux 2.6.34內核
    《Linux內核設計與實現（第三版）》中所用的內核版本為2.6.34，這個版本太舊了，在高版本系統中編譯各種不支持，所以選了Ubuntu 14.04進行編譯。 編譯準備 編譯環境：Ubuntu 14.04 GCC版本：4.8.4 編譯的內核版本：Linux 2.6.34 內核源碼下載：https:/ ...
  • PE 文件結構圖
    最近在進行免殺的學習，在《黑客免殺攻防》這本書中找到了非常好的關於PE文件的描述，雖然書比較古老的，但是裡面的內容是非常精細和優秀的。它的附頁中有非常清晰的PE文件結構圖，可是翻看比較麻煩，撕下來又可惜，於是我今天對著附頁的圖用excel重新畫了一個。這些工作我平時做的比較少，所以消耗了比較久的時間 ...
  • 操作系統中文件系統的實現和分配方式探析（上）
    本文主要討論了操作系統中文件系統的實現和分配方式。首先介紹了虛擬文件系統（VFS）作為中間層，統一了不同文件系統的介面。然後介紹了文件的物理結構，包括文件塊和邏輯塊之間的映射關係。接著詳細討論了連續分配方式的特點和優缺點，包括順序訪問和隨機訪問的效率，以及磁碟空間碎片和文件長度擴展不方便的問題。最後... ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類