題目1: App 使用頻率分析

現(xiàn)有一張用戶使用 App 時間表 middle_app_login，middle_app_login 表的數(shù)據(jù)如下表所示：

mysql> SELECT * FROM middle_app_login;
-- user_id（用戶ID）：VARCHAR start_time（登錄App時間）：DATETIME end_time（退出App時間）：DATETIME
+---------+---------------------+---------------------+
| user_id | start_time          | end_time            |
+---------+---------------------+---------------------+
| u001    | 2021-04-01 10:12:30 | 2021-04-01 11:13:21 |
| u002    | 2021-04-02 08:40:21 | 2021-04-02 10:13:41 |
| u003    | 2021-04-02 15:31:01 | 2021-04-02 15:54:42 |
| u001    | 2021-04-04 13:25:40 | 2021-04-04 17:52:46 |
| u003    | 2021-04-06 07:10:20 | 2021-04-06 08:03:15 |
| u001    | 2021-04-09 18:20:34 | 2021-04-09 18:23:58 |
| u001    | 2021-04-10 14:25:55 | 2021-04-10 15:01:25 |
+---------+---------------------+---------------------+
7 rows in set (0.00 sec)

【題目1】根據(jù)該表統(tǒng)計出每個用戶每次退出App與下一次登錄App間隔的平均時間，如果用戶只登錄過一次App，則不統(tǒng)計，要求輸出的平均時間的單位為分鐘，并將其四舍五入保留一位小數(shù)。輸出內(nèi)容包括：user_id（用戶ID）、avg_minute（平均間隔時間），結(jié)果樣例如下圖所示：

【題目1解析】本題使用LEAD()函數(shù)對每個用戶登錄App的時間進行分組排序，生成新的一列，以構(gòu)造出上一次退出App的時間與下一次登錄App的時間在同一行的表格結(jié)構(gòu)，方便之后進行處理。然后篩選出為非空的行，使用TIMESTAMPDIFF()函數(shù)計算start_time_lead與end_time的分鐘差，再求平均值，并將其四舍五入保留一位小數(shù)即可得到結(jié)果。涉及知識點：子查詢、日期/時間處理函數(shù)、窗口函數(shù)、空值處理、小數(shù)保留、分組聚合。參考代碼如下：

mysql> -- ① 按照解析的寫法
mysql> SELECT user_id->      , ROUND(AVG(TIMESTAMPDIFF(MINUTE, end_time, start_time_lead)), 1) AS avg_minute-> FROM (SELECT user_id->            , start_time->            , end_time->            , LEAD(start_time, 1) OVER (PARTITION BY user_id ORDER BY start_time) AS start_time_lead->       FROM middle_app_login) a-> WHERE start_time_lead IS NOT NULL-> GROUP BY user_id;
+---------+------------+
| user_id | avg_minute |
+---------+------------+
| u001    |     4293.3 |
| u003    |     5235.0 |
+---------+------------+
2 rows in set (0.00 sec)mysql> -- ② 第二種寫法
mysql> SELECT user_id, ROUND(AVG(end_time_lag), 1) AS avg_minute-> FROM (SELECT a1.user_id,->              TIMESTAMPDIFF(MINUTE, LAG(end_time, 1) OVER (PARTITION BY a1.user_id ORDER BY start_time), a1.start_time->                  ) AS end_time_lag->       FROM middle_app_login a1->                INNER JOIN (SELECT user_id FROM middle_app_login GROUP BY user_id HAVING COUNT(*) > 1) a2->                           ON a1.user_id = a2.user_id) a-> WHERE a.end_time_lag IS NOT NULL-> GROUP BY user_id;
+---------+------------+
| user_id | avg_minute |
+---------+------------+
| u001    |     4293.3 |
| u003    |     5235.0 |
+---------+------------+
2 rows in set (0.00 sec)

題目2: App 下載情況統(tǒng)計

現(xiàn)有一張 App 累計下載情況表 middle_app_download，該表記錄了應(yīng)用商品中 App 累計下載次數(shù)的信息，middle_app_download 表的數(shù)據(jù)如下所示：

mysql> SELECT * FROM middle_app_download;
-- app_id（AppID）：VARCHAR app_type（App類型）：VARCHAR download（下載次數(shù)）：INT
+--------+----------+----------+
| app_id | app_type | download |
+--------+----------+----------+
| a001   | A        |    12432 |
| a002   | B        |     9853 |
| a003   | A        |     1924 |
| a004   | C        |     2679 |
| a005   | C        |    29104 |
| a006   | A        |    10235 |
| a007   | B        |     5704 |
| a008   | B        |     2850 |
| a009   | B        |     8235 |
| a010   | C        |     9746 |
+--------+----------+----------+
10 rows in set (0.00 sec)

【題目2】查詢不同類型App的平均下載次數(shù)，需要將下載次數(shù)排在前10%與后10%的App排除在外。輸出內(nèi)容包括：app_type（App類型）、avg_download（平均下載次數(shù)），結(jié)果樣例如下圖所示：

【題目2解析】使用RANK()函數(shù)生成新的一列作為下載量排名(ranking)，將該部分作為子查詢內(nèi)部，并在子查詢外部通過WHERE篩選出符合要求的記錄，分組統(tǒng)計平均下載次數(shù)即可。涉及知識點：子查詢、窗口函數(shù)、空值處理、分組聚合。參考代碼如下：

mysql> SELECT a.app_type, AVG(a.download) as avg_download-> FROM (SELECT app_id, app_type, download, RANK() OVER (ORDER BY download DESC ) AS download_rank->       FROM middle_app_download) a-> WHERE a.download_rank > (SELECT COUNT(*) FROM middle_app_download) * 0.1->   AND a.download_rank < (SELECT COUNT(*) FROM middle_app_download) * 0.9-> GROUP BY a.app_type;

題目3: 尋找活躍學(xué)習(xí)者

現(xiàn)有一張用戶學(xué)習(xí)打卡表 middle_active_learning，middle_active_learning 表中數(shù)據(jù)如下所示：

mysql> SELECT * FROM middle_active_learning;
-- user_id（用戶ID）：VARCHAR study_date（打卡日期）：DATE
+---------+------------+
| user_id | study_date |
+---------+------------+
| u001    | 2021-04-01 |
| u002    | 2021-04-01 |
| u003    | 2021-04-03 |
| u001    | 2021-04-06 |
| u003    | 2021-04-07 |
| u001    | 2021-04-12 |
| u001    | 2021-04-13 |
| u002    | 2021-04-14 |
| u001    | 2021-04-23 |
| u002    | 2021-04-24 |
| u001    | 2021-04-26 |
| u003    | 2021-04-27 |
| u002    | 2021-04-30 |
+---------+------------+
13 rows in set (0.00 sec)

【題目3】根據(jù)該表統(tǒng)計2021年4月中每周都學(xué)習(xí)打卡的用戶。輸出內(nèi)容包括： user_id（用戶ID），結(jié)果樣例如下圖所示：

【題目3解析】使用WEEKOFYEAR函數(shù)獲取周數(shù)，限制study_date為2021年4月，由于用戶可能一周內(nèi)打卡多次，因此使用DISTINCT進行去重，為之后的統(tǒng)計操作做鋪墊，通過GROUP BY對用戶進行分組，統(tǒng)計出打卡周的數(shù)量等于5的用戶(2021年4月跨越5周)，即可得到每周打卡的用戶。涉及知識點：子查詢、DISTINCT、日期/時間處理函數(shù)。參考代碼如下：

mysql> SELECT a.user_id-> FROM (SELECT DISTINCT user_id->                     , WEEKOFYEAR(study_date) AS study_week->       FROM middle_active_learning->       WHERE study_date >= '2021-04-01'->         AND study_date <= '2021-04-30') a-> GROUP BY a.user_id-> HAVING COUNT(a.study_week) = 5;

題目4: 商品分類整理

現(xiàn)有一張商品分類表 middle_commodity_classification，middle_commodity_classification 表的數(shù)據(jù)如下表所示：

mysql> SELECT * FROM middle_commodity_classification;
-- current_category（商品當(dāng)前分類）：VARCHAR parent_category（商品父類別）：VARCHAR
+------------------+-----------------+
| current_category | parent_category |
+------------------+-----------------+
| 刀               | 廚具            |
| 廚具             | 生活用品        |
| 碗               | 餐具            |
| 水果刀           | 刀              |
| 剔骨刀           | 刀              |
| 餐具             | 生活用品        |
| 湯碗             | 碗              |
+------------------+-----------------+
7 rows in set (0.00 sec)

【題目4】查詢得到下圖所示的結(jié)果樣例。輸出內(nèi)容包括：三級類目、二級類目、一級類目、根類目，結(jié)果樣例如下圖所示：

【題目4解析】本題是分類關(guān)系的整理，顯示的結(jié)果樣例中有4層類別關(guān)系，需要通過3次表的自連接來實現(xiàn)。涉及知識點：自連接。參考代碼如下：

mysql> SELECT m1.current_category AS '三級類目',->        m1.parent_category  AS '二級類目',->        m2.parent_category  AS '一級類目',->        m3.parent_category  AS '根目錄'-> FROM middle_commodity_classification m1,->      middle_commodity_classification m2,->      middle_commodity_classification m3-> WHERE m1.parent_category = m2.current_category->   AND m2.parent_category = m3.current_category;

題目5: 商品銷售分析

現(xiàn)有一張商品信息表 middle_commodity_info，該表記錄了商品的基礎(chǔ)信息，middle_commodity_info 數(shù)據(jù)如下所示：

mysql> SELECT * FROM middle_commodity_info;
-- sku_id（商品SKU）：VARCHAR commodity_category（商品類別）：VARCHAR director（商品銷售負(fù)責(zé)人）：VARCHAR
+--------+--------------------+----------+
| sku_id | commodity_category | director |
+--------+--------------------+----------+
| u001   | c001               | a001     |
| u003   | c002               | a001     |
| u002   | c003               | a002     |
+--------+--------------------+----------+
3 rows in set (0.00 sec)

還有一張商品銷售金額表 middle_commodity_sale，該表記錄了每天商品的銷售情況，middle_commodity_sale 數(shù)據(jù)如下所示：

mysql> SELECT * FROM middle_commodity_sale;
-- date(日期)：DATE sku_id（商品SKU）：VARCHAR sales（商品銷售金額）：INT
+------------+--------+-------+
| date       | sku_id | sales |
+------------+--------+-------+
| 2020-12-20 | u001   | 12000 |
| 2020-12-20 | u002   |  8000 |
| 2020-12-20 | u003   | 11000 |
| 2020-12-21 | u001   | 20000 |
| 2020-12-21 | u003   | 16000 |
| 2020-12-22 | u003   | 11000 |
| 2020-12-22 | u001   | 34000 |
| 2020-12-22 | u002   | 11000 |
| 2020-12-23 | u003   | 18000 |
| 2020-12-23 | u001   | 30000 |
+------------+--------+-------+
10 rows in set (0.00 sec)

【題目5】查詢商品銷售負(fù)責(zé)人為a001在2020年每個商品類別銷量最高的兩天的信息，輸出內(nèi)容包括：commodity_category（商品分類）、date（日期）、total_sales（銷量），結(jié)果樣例如下圖所示：

【題目5】參考代碼如下：

mysql> SELECT commodity_category->      , `date`->      , total_sales-> FROM (->          SELECT commodity_category->               , `date`->               , RANK() OVER (PARTITION BY commodity_category ORDER BY total_sales DESC) AS ranking->               , total_sales->          FROM (->                   SELECT b.commodity_category->                        , a.`date`->                        , SUM(a.sales) AS total_sales->                   FROM middle_commodity_sale a->                            JOIN middle_commodity_info b->                                 ON a.sku_id = b.sku_id->                   WHERE b.director = 'a001'->                     AND YEAR(a.`date`) = 2020->                   GROUP BY b.commodity_category->                          , a.`date`->               ) c->      ) d-> WHERE ranking <= 2;

題目6: 網(wǎng)約車司機收益統(tǒng)計

現(xiàn)有一張網(wǎng)約車訂單表 middle_car_order，該表記錄了某天的網(wǎng)約車訂單相關(guān)信息，middle_car_order 數(shù)據(jù)如下表所示：

mysql> SELECT * FROM middle_car_order;
-- order_id（訂單ID）：VARCHAR driver_id（司機ID）：VARCHAR order_amount（訂單金額）：DOUBLE
+----------+-----------+--------------+
| order_id | driver_id | order_amount |
+----------+-----------+--------------+
| o001     | d001      |         15.6 |
| o002     | d002      |         36.5 |
| o003     | d001      |         30.1 |
| o004     | d002      |         10.6 |
| o005     | d001      |         26.2 |
| o006     | d001      |         14.6 |
| o007     | d003      |         28.9 |
| o008     | d001      |          8.8 |
| o009     | d002      |         13.3 |
| o010     | d001      |         29.4 |
+----------+-----------+--------------+
10 rows in set (0.00 sec)

【題目6】司機的收入為訂單金額的80%（表中訂單金額的單位為元），如果司機當(dāng)天的訂單數(shù)量>=5且總訂單金額>=100，則能收到額外補貼10元。請統(tǒng)計當(dāng)天各司機的收入，并將結(jié)果按照收入降序排列且四舍五入保留兩位小數(shù)。輸出內(nèi)容包括：driver_id（司機ID）、total_order（總訂單量）、total_income（總收入），結(jié)果樣例如下圖所示：

【題目6】參考代碼如下：

mysql> SELECT a.driver_id,->        a.total_order,->        CASE->            WHEN total_order >= 5 AND total_amount >= 100 THEN ROUND(total_amount * 0.8 + 10, 2)->            ELSE ROUND(total_amount * 0.8, 2) END AS 'total_income'-> FROM (SELECT driver_id, COUNT(driver_id) AS 'total_order', SUM(order_amount) AS 'total_amount'->       FROM middle_car_order->       GROUP BY driver_id) a ORDER BY total_income DESC;

題目7: 網(wǎng)站登錄時間間隔統(tǒng)計

現(xiàn)有一張網(wǎng)站登錄情況表 middle_login_info，該表記錄了所有用戶的網(wǎng)站登錄信息，middle_login_info 表的數(shù)據(jù)如下所示：

mysql> SELECT * FROM  middle_login_info;
-- user_id（用戶ID）：VARCHAR login_time（用戶登錄日期）：DATE
+---------+------------+
| user_id | login_time |
+---------+------------+
| a001    | 2021-01-01 |
| b001    | 2021-01-01 |
| a001    | 2021-01-03 |
| a001    | 2021-01-06 |
| a001    | 2021-01-07 |
| b001    | 2021-01-07 |
| a001    | 2021-01-08 |
| a001    | 2021-01-09 |
| b001    | 2021-01-09 |
| b001    | 2021-01-10 |
| b001    | 2021-01-15 |
| a001    | 2021-01-16 |
| a001    | 2021-01-18 |
| a001    | 2021-01-19 |
| b001    | 2021-01-20 |
| a001    | 2021-01-23 |
+---------+------------+
16 rows in set (0.00 sec)

【題目7】計算每個用戶登錄日期間隔小于5天的次數(shù)。輸出內(nèi)容包括：user_id（用戶ID）、num（用戶登錄日期間隔小于5天的次數(shù)），結(jié)果樣例如下圖所示：

【題目7】參考代碼如下：

mysql> SELECT a.user_id, COUNT(*) AS 'num'-> FROM (SELECT user_id,->              login_time,->              TIMESTAMPDIFF(DAY, LAG(login_time) OVER (PARTITION BY user_id ORDER BY login_time),->                            login_time) AS date_diff->       FROM middle_login_info) a-> WHERE a.date_diff < 5-> GROUP BY a.user_id;

題目8: 不同區(qū)域商品收入統(tǒng)計

現(xiàn)有一張不同城市的商品收入情況表 middle_sale_volume，該表記錄了年份和區(qū)域等信息，middle_sale_volume 數(shù)據(jù)如下表所示：

mysql> SELECT * FROM middle_sale_volume;
-- year（年份）：YEAR region（區(qū)域）：VARCHAR city（城市）：VARCHAR money（收入）：INT
+------+--------+------+-------+
| year | region | city | money |
+------+--------+------+-------+
| 2018 | 東區(qū)   | A 市 |  1125 |
| 2019 | 東區(qū)   | A 市 |  1305 |
| 2020 | 東區(qū)   | A 市 |  1623 |
| 2018 | 東區(qū)   | C 市 |   845 |
| 2019 | 東區(qū)   | C 市 |   986 |
| 2020 | 東區(qū)   | C 市 |  1134 |
| 2018 | 西區(qū)   | M 市 |   638 |
| 2019 | 西區(qū)   | M 市 |  1490 |
| 2020 | 西區(qū)   | M 市 |  1120 |
| 2018 | 西區(qū)   | V 市 |  1402 |
| 2019 | 西區(qū)   | V 市 |  1209 |
| 2020 | 西區(qū)   | V 市 |  1190 |
+------+--------+------+-------+
12 rows in set (0.00 sec)

【題目8】統(tǒng)計每個區(qū)域的總收入和平均收入，并將結(jié)果四舍五入保留一位小數(shù)。輸出內(nèi)容包括：year（年份）、不同區(qū)域的總收入和平均收入，結(jié)果樣例如下圖所示：

【題目8】參考代碼如下：

-- 第①種寫法
mysql> SELECT a.`year`->      , ROUND(SUM(IF(a.region = '東區(qū)', a.money, 0)), 1)->     AS '東區(qū)總收入'->      , ROUND(SUM(IF(a.region = '西區(qū)', a.money, 0)), 1)->     AS '西區(qū)總收入'->      , ROUND(SUM(IF(a.region = '東區(qū)', a.money, 0)) / SUM(a.east_area), 1)->     AS '東區(qū)平均收入'->      , ROUND(SUM(IF(a.region = '西區(qū)', a.money, 0)) / SUM(a.west_area), 1)->     AS '西區(qū)平均收入'-> FROM (->          SELECT `year`->               , region->               , money->               , IF(region = '東區(qū)', 1, 0) AS east_area->               , IF(region = '西區(qū)', 1, 0) AS west_area->          FROM sale_volume->          GROUP BY `year`->                 , region->                 , money->      ) AS a-> GROUP BY a.`year`;
-- 第②種寫法
mysql> SELECT a.year,->        ROUND(a.收入, 1)   AS '東區(qū)總收入',->        ROUND(b.收入, 1)   AS '西區(qū)總收入',->        ROUND(a.平均收入, 1) AS '東區(qū)平均收入',->        ROUND(b.平均收入, 1) AS '西區(qū)平均收入'-> FROM (SELECT year,->              region,->              SUM(money) AS '收入',->              AVG(money) AS '平均收入'->       FROM middle_sale_volume->       GROUP BY year, region) a->          INNER JOIN (SELECT year,->                             region,->                             SUM(money) AS '收入',->                             AVG(money)    '平均收入'->                      FROM middle_sale_volume->                      GROUP BY year, region) b ON a.region < b.region AND a.year = b.year;

題目9: 信貸逾期情況統(tǒng)計

現(xiàn)有一張用戶貸款情況表 middle_credit_overdue，middle_credit_overdue 表中的數(shù)據(jù)如下所示：

mysql> SELECT * FROM middle_credit_overdue;
-- user_id（用戶ID）：VARCHAR overdue_date（貸款逾期日期）：DATE
+---------+--------------+
| user_id | overdue_date |
+---------+--------------+
| u001    | 2020-10-20   |
| u002    | 2020-11-03   |
| u003    | 2020-10-04   |
| u004    | 2021-01-05   |
| u005    | 2021-01-15   |
| u006    | 2020-09-04   |
| u007    | 2021-01-03   |
| u008    | 2020-12-24   |
| u009    | 2020-12-10   |
+---------+--------------+
9 rows in set (0.00 sec)

【題目9】統(tǒng)計日期截至2021年1月20日，不同逾期月份的逾期1-29天，逾期30-59天和逾期60天以上的樣本個數(shù)。輸出內(nèi)容包括：overdue_month（逾期月份）、逾期1~29天，逾期30~59天，逾期60天以上，結(jié)果樣例如下圖所示：

【題目9】參考代碼如下：

-- 第①種寫法參考:
mysql> SELECT LEFT(overdue_date, 7),->        SUM(CASE->                WHEN TIMESTAMPDIFF(DAY, overdue_date, '2021-01-20') BETWEEN 1 AND 29 THEN 1->                ELSE 0 END) AS '逾期1-29天',->        SUM(CASE->                WHEN TIMESTAMPDIFF(DAY, overdue_date, '2021-01-20') BETWEEN 30 AND 59 THEN 1->                ELSE 0 END) AS '逾期30-59天',->        SUM(CASE->                WHEN TIMESTAMPDIFF(DAY, overdue_date, '2021-01-20') > 60 THEN 1->                ELSE 0 END) AS '逾期60天以上'-> FROM middle_credit_overdue-> GROUP BY LEFT(overdue_date, 7)-> ORDER BY LEFT(overdue_date, 7)->         DESC;
-- 第②種寫法參考:
mysql> SELECT overdue_month->      , COUNT(CASE->                  WHEN overdue_days >= 1 AND overdue_days < 30->                      THEN user_id END)->     AS '逾期 1-29 天'->      , COUNT(CASE->                  WHEN overdue_days >= 30 AND overdue_days < 60->                      THEN user_id END)->     AS '逾期 30-59 天'->      , COUNT(CASE->                  WHEN overdue_days >= 60->                      THEN user_id END)->     AS '逾期 60 天以上'-> FROM (->          SELECT user_id->               , DATE_FORMAT(overdue_date, '%Y-%m') AS overdue_month->               , DATEDIFF('2021-01-20', overdue_date)->                                                    AS overdue_days->          FROM middle_credit_overdue->      ) a-> GROUP BY overdue_month-> ORDER BY overdue_month DESC;

至此今天的學(xué)習(xí)就到此結(jié)束了，筆者在這里聲明，筆者寫文章只是為了學(xué)習(xí)交流，以及讓更多學(xué)習(xí)數(shù)據(jù)庫的讀者少走一些彎路，節(jié)省時間，并不用做其他用途，如有侵權(quán)，聯(lián)系博主刪除即可。感謝您閱讀本篇博文，希望本文能成為您編程路上的領(lǐng)航者。祝您閱讀愉快！

---

????好書不厭讀百回，熟讀課思子自知。而我想要成為全場最靚的仔，就必須堅持通過學(xué)習(xí)來獲取更多知識，用知識改變命運，用博客見證成長，用行動證明我在努力。
????如果我的博客對你有幫助、如果你喜歡我的博客內(nèi)容，請 點贊、評論、收藏 一鍵三連哦！聽說點贊的人運氣不會太差，每一天都會元氣滿滿呦！如果實在要白嫖的話，那祝你開心每一天，歡迎常來我博客看看。
?編碼不易，大家的支持就是我堅持下去的動力。點贊后不要忘了 關(guān)注 我哦！