----SQL157 平均播放進度大于60%的視頻類別
計算各類視頻的平均播放進度，將進度大于60%的類別輸出。
注：
播放進度=播放時長÷視頻時長*100%，當播放時長大于視頻時長時，播放進度均記為100%。
結果保留兩位小數，并按播放進度倒序排序。
輸出：
tag|avg_play_progress

表的創建及數據插入：

DROP TABLE IF EXISTS tb_user_video_log, tb_video_info;
CREATE TABLE tb_user_video_log
(id         INT PRIMARY KEY identity,-- '自增ID',uid        INT NOT NULL,-- '用戶ID',video_id   INT NOT NULL,-- '視頻ID',start_time datetime,-- '開始觀看時間',end_time   datetime,-- '結束觀看時間',if_follow  TINYINT,-- '是否關注',if_like    TINYINT,-- '是否點贊',if_retweet TINYINT,-- '是否轉發',comment_id INT,-- '評論ID'
);CREATE TABLE tb_video_info
(id           INT PRIMARY KEY identity,-- '自增ID',video_id     INT UNIQUE  NOT NULL,-- '視頻ID',author       INT         NOT NULL,-- '創作者ID',tag          VARCHAR(16) NOT NULL,-- '類別標簽',duration     INT         NOT NULL,-- '視頻時長(秒數)',release_time datetime    NOT NULL,-- '發布時間'
);INSERT INTO tb_user_video_log(uid, video_id, start_time, end_time, if_follow, if_like, if_retweet, comment_id)
VALUES (101, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:30', 0, 1, 1, null),(102, 2001, '2021-10-01 10:00:00', '2021-10-01 10:00:21', 0, 0, 1, null),(103, 2001, '2021-10-01 11:00:50', '2021-10-01 11:01:20', 0, 1, 0, 1732526),(102, 2002, '2021-10-01 11:00:00', '2021-10-01 11:00:30', 1, 0, 1, null),(103, 2002, '2021-10-01 10:59:05', '2021-10-01 11:00:05', 1, 0, 1, null);INSERT INTO tb_video_info(video_id, author, tag, duration, release_time)
VALUES (2001, 901, '影視', 30, '2021-01-01 7:00:00'),(2002, 901, '美食', 60, '2021-01-01 7:00:00'),(2003, 902, '旅游', 90, '2020-01-01 7:00:00');

查詢如下：

 select tag,format(CAST(Convert(decimal(18,4),sum(watch_time)*1.0/sum(duration)) * 100 as decimal(10,2)), 'N2') + '%' as avg_play_progressfrom (select tv.tag,datediff(second,start_time,end_time)  as watch_time,tv.durationfrom tb_user_video_log tujoin tb_video_info tvon tu.video_id = tv.video_id)tgroup by tagorder by avg_play_progress desc

使用嵌套方便在寫查詢時理清思路，可優化查詢如下（不嵌套查詢）：

select tv.tag,format(CAST(avg(iif(datediff(second,start_time,end_time) >= duration,100,Convert(decimal(18,2),datediff(second,start_time,end_time)*1.0/duration)*100)) as decimal(10,2)), 'N2') + '%' as avg_play_progressfrom tb_user_video_log tujoin tb_video_info tvon tu.video_id = tv.video_idgroup by tagorder by avg_play_progress desc

做題小結：
① FORMAT函數
FORMAT函數用于將數據格式化為指定的字符串表示形式。它可以應用于各種數據類型，如日期、時間、數值等。
FORMAT函數的基本語法如下：

FORMAT (value, format)

value 是要格式化的表達式或列，format 是定義格式的字符串。
FORMAT函數的格式字符串的確切用法取決于輸入表達式的數據類型和所需的輸出格式，可以根據實際需求進行調整。
要注意：FORMAT函數在處理大量數據時可能會影響性能，如果需要對大型數據集進行格式化，建議在客戶端應用程序或報表中進行格式化，而不是在數據庫查詢中使用FORMAT函數。

【舉個🌰】
有查詢如下：

select 0.8500 as '轉換前', format(cast(0.8500 * 100 as decimal(10,2)), 'N2') + '%' as '轉換后'

執行結果：

轉換前	轉換后
0.8500	85.00%

在此查詢中：
① 將待轉換的數值使用 CAST 函數將其乘以 100，轉換為 DECIMAL(10,2) 類型，結果為 85.00。② 使用 FORMAT 函數將結果格式化為帶有兩位小數的字符串，'N2' 表示將結果作為數字格式化并保留2位小數。具體來說：‘N’ 表示數字格式，‘2’ 表示小數點后保留的位數。
③ 最后在末尾追加百分號 '% '。