讀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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類