滴滴SQL面試題之打車業務問題如何分析

一、題目

二、步驟

1、資料類型轉化

2、提取2020年8月各城市每天的司機數，快車訂單量和快車流水資料

①2020年8月各城市每天的司機數

②快車訂單量和快車流水資料

③小結

3、提取2020年8月和9月，每個月的北京市新老司機（首單日期在當月為新司機）的司機數、線上時長和TPH（訂單量/線上時長）資料。

①2020年8月和9月的新司機的司機數

②線上時長

③訂單量

④總結

4、分别提取司機數大于20，司機總線上時長大于2小時，訂單量大于1，乘客數大于1的城市名稱資料。

①司機數大于20的城名稱

②司機線上總時長大于2小時

③ 訂單量大于1

④乘客數大于1

⑤小結

一、題目

公司A是的app（類似滴滴、uber）為使用者提供打車服務。現有四張表，分别是“司機資料”表，“訂單資料”表，“線上時長資料”表，“城市比對資料”表。業務問題：

1. 提取2020年8月各城市每天的司機數、快車訂單量和快車流水資料。

2. 提取2020年8月和9月，每個月的北京市新老司機（首單日期在當月為新司機）的司機數、線上時長和TPH（訂單量/線上時長）資料。

3. 分别提取司機數大于20，司機總線上時長大于2小時，訂單量大于1，乘客數大于1的城市名稱資料。

二、步驟

1、資料類型轉化

通過觀察資料庫表結構可知，關于時間的存儲類型是varchar，但是需要提取到月份，是以需要将時間列的類型轉換成date的特定的格式（資料中涉及多個時間）

update 司機資料 
set 日期=date_format(日期,'%Y-%m-%d');

2、提取2020年8月各城市每天的司機數，快車訂單量和快車流水資料

①2020年8月各城市每天的司機數

根據題目意思以及表結構，需要将城市比對資料與司機資料兩張表進行連接配接，連接配接的條件是司機id相等

一個限制條件——2020年8月，也就是司機資料表的日期在2020-08-01到2020-08-31範圍之内

一個聚合——對司機資料表中的司機id進行聚合count()

兩個分組——8月各城市，也就是8月的每天以及每個城市的情況，是以需要對日期以及城市id進行分組

select b.`城市名稱`,a.`日期`,COUNT(司機id) as '司機數'from 
`司機資料` as a left join `城市比對資料` as b
on a.`城市id`=b.`城市id`
where a.`日期` between '2020-08-01' and '2020-08-31'
group by a.`城市id`,a.`日期`

②快車訂單量和快車流水資料

在四個表中，隻有訂單資料中含有快車資料，但是表中沒有城市id和司機id，是以需要跟其他表進行關聯才能得到結果

兩個限制條件——一個是時間上的限制，一個就是訂單的類型

兩個聚合——一個是訂單量，一個是流水資料

訂單量也就是對産品線id的求個數和

流水資料是對表中流水的求和

兩個分組——8月各城市，也就是8月的每天以及每個城市的情況，是以需要對日期以及城市id進行分組

select c.`城市名稱`,a.`日期`,COUNT(a.`産品線id`) as '快車訂單量',SUM(a.`流水`) as '快車流水資料' from 
`訂單資料` as a left join `司機資料` as b
on a.`司機id`=b.`司機id`
left join `城市比對資料` as c
on b.`城市id` = c.`城市id`
where a.`日期` between '2020-08-01' and '2020-08-31' and a.`産品線id`=3
GROUP BY c.`城市id`,b.`日期`

③小結

前兩步已經分别求出了想要的資料，但是因為判斷條件的原因，不能将兩張表給聯合起來，是以隻能将兩個表作為子表，然後再去查子表得到資料

3、提取2020年8月和9月，每個月的北京市新老司機（首單日期在當月為新司機）的司機數、線上時長和TPH（訂單量/線上時長）資料。

①2020年8月和9月的新司機的司機數

根據資料可知，所需要的資料全在一張表中，是以不需要連表

三個限制條件——時間、新司機（首單日期在當月），城市為北京

上一問的時間條件是8月，此問的是8月和9月也就是将時間的範圍擴大到2020-09-31

新司機的條件是首單日期在當月，也就是用時間函數得到首單日期和日期的月份相等

通過城市比對資料的城市id确定城市在北京

分組——隻需要按照時間進行分組即可

SELECT
日期,
COUNT(`司機id` ) AS '新司機數' 
FROM
  `司機資料` 
WHERE
  YEAR ( `首次完成訂單時間` )= YEAR ( `日期` ) 
  AND MONTH ( `日期` ) = MONTH ( `首次完成訂單時間` ) 
  AND ( `日期` BETWEEN '2020-08-01' AND '2020-09-31' ) 
  AND `城市id` = 100000 
GROUP BY
  DATE_FORMAT(`日期`, '%Y-%m' );

②線上時長

線上時長在線上時長資料的表中而訂單完成時間等在司機資料表中，是以需要對兩表進行關聯，關聯的條件就是司機id

也就是在第一問的條件基礎上形成的子表與線上時長資料表進行關聯

線上時長需要一個求和的計算

SELECT
  a.`日期`,
  sum( b.`線上時長` ) AS '線上時長' 
FROM
  (
  SELECT
    * 
  FROM
    `司機資料` 
  WHERE
    YEAR ( `首次完成訂單時間` )= YEAR ( `日期` ) 
    AND MONTH ( `日期` ) = MONTH ( `首次完成訂單時間` ) 
    AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
    AND `城市id` = 100000 
  ) AS a
  LEFT JOIN `線上時長資料` AS b ON a.`司機id` = b.`司機id` 
GROUP BY
  DATE_FORMAT(
    a.`日期`,
  '%Y-%m')

③訂單量

訂單量的計算與線上總時長的原理是一緻的，隻是關聯的表不一樣而已

SELECT
  a.`日期`,
  COUNT(b.`訂單id`) as '訂單量'
FROM
  (
  SELECT
    * 
  FROM
    `司機資料` 
  WHERE
    YEAR ( `首次完成訂單時間` )= YEAR ( `日期` ) 
    AND MONTH ( `日期` ) = MONTH ( `首次完成訂單時間` ) 
    AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
    AND `城市id` = 100000 
  ) AS a
  LEFT JOIN `訂單資料` AS b ON a.`司機id` = b.`司機id` 
GROUP BY
  DATE_FORMAT(
    a.`日期`,
  '%Y-%m')

④總結

有了前面查出來的資料之後可以将其作為子表，然後再通過查子表得到最後的資料

SELECT
  a.`新司機THP` AS '新司機THP',
  b.`老司機THP` AS '老司機THP' 
FROM
  (
  SELECT
    b.`訂單量` / a.`線上時長` AS '新司機THP' 
  FROM
    (
    SELECT
      sum( b.`線上時長` ) AS '線上時長' 
    FROM
      (
      SELECT
        * 
      FROM
        `司機資料` 
      WHERE
        YEAR ( `首次完成訂單時間` )= YEAR ( `日期` ) 
        AND MONTH ( `日期` ) = MONTH ( `首次完成訂單時間` ) 
        AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
        AND `城市id` = 100000 
      ) AS a
      LEFT JOIN `線上時長資料` AS b ON a.`司機id` = b.`司機id` 
    GROUP BY
    DATE_FORMAT( a.`日期`, '%Y-%m' )) AS a,
    (
    SELECT
      COUNT( b.`訂單id` ) AS '訂單量' 
    FROM
      (
      SELECT
        * 
      FROM
        `司機資料` 
      WHERE
        YEAR ( `首次完成訂單時間` )= YEAR ( `日期` ) 
        AND MONTH ( `日期` ) = MONTH ( `首次完成訂單時間` ) 
        AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
        AND `城市id` = 100000 
      ) AS a
      LEFT JOIN `訂單資料` AS b ON a.`司機id` = b.`司機id` 
    GROUP BY
    DATE_FORMAT( a.`日期`, '%Y-%m' )) AS b 
  ) AS a,
  (
  SELECT
    b.`訂單量` / a.`線上時長` AS '老司機THP' 
  FROM
    (
    SELECT
      sum( b.`線上時長` ) AS '線上時長' 
    FROM
      (
      SELECT
        * 
      FROM
        `司機資料` 
      WHERE
        YEAR ( `首次完成訂單時間` )= YEAR ( `日期` ) 
        AND MONTH ( `日期` ) = MONTH ( `首次完成訂單時間` ) 
        AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
        AND `城市id` = 100000 
      ) AS a
      LEFT JOIN `線上時長資料` AS b ON a.`司機id` = b.`司機id` 
    GROUP BY
    DATE_FORMAT( a.`日期`, '%Y-%m' )) AS a,
    (
    SELECT
      COUNT( b.`訂單id` ) AS '訂單量' 
    FROM
      (
      SELECT
        * 
      FROM
        `司機資料` 
      WHERE
        YEAR ( `首次完成訂單時間` ) <> YEAR ( `日期` ) 
        AND MONTH ( `日期` ) <> MONTH ( `首次完成訂單時間` ) 
        AND ( `日期` BETWEEN '2020-08-01' AND '2020-08-31' OR `日期` BETWEEN '2020-09-01' AND '2020-09-31' ) 
        AND `城市id` = 100000 
      ) AS a
      LEFT JOIN `訂單資料` AS b ON a.`司機id` = b.`司機id` 
    GROUP BY
    DATE_FORMAT( a.`日期`, '%Y-%m' )) AS b 
  ) AS b

4、分别提取司機數大于20，司機總線上時長大于2小時，訂單量大于1，乘客數大于1的城市名稱資料。

①司機數大于20的城市名稱

司機的資料在司機資料表中，城市名稱在城市比對資料表中，是以需要進行連表，連表的條件就是城市id

還需要對城市id進行分組，然後統計司機個數，并且其數量還要大于20，因為是分組之後的統計，是以判斷條件不能用where，而是要用having，且在分組後面

SELECT
    b.`城市名稱`,
    COUNT( a.`司機id` ) AS '城市司機數' 
  FROM
    `司機資料` AS a
    LEFT JOIN `城市比對資料` AS b ON a.`城市id` = b.`城市id` 
  GROUP BY
    b.`城市id` 
  HAVING
    COUNT( a.`司機id` )> 20

②司機線上總時長大于2小時

線上時長資料，城市id兩表之間是沒有可以連接配接的鍵的，是以需要通過司機資料表做為中間表進行關聯

先對線上時長表中的資料進行對司機id的分組然後聚合得到其線上總時長，然後再篩選大于2小時的資料

将篩選過的表再與司機資料表以及城市資訊表進行關聯

③ 訂單量大于1

其思路與求線上總時長是一緻的

SELECT
    a.`司機id`,
    a.`訂單量`,
    c.`城市名稱` 
  FROM
    ( SELECT `司機id`, COUNT( `司機id` ) AS `訂單量` FROM `訂單資料` GROUP BY `司機id` HAVING COUNT( `司機id` ) > 1 ) AS a
    LEFT JOIN ( SELECT `司機id`, `城市id` FROM `司機資料` ) AS b ON a.`司機id` = b.`司機id`
    JOIN `城市比對資料` AS c ON b.`城市id` = c.`城市id`

④乘客數大于1

SELECT
    a.`司機id`,
    a.`乘客數`,
    c.`城市名稱` 
  FROM
    ( SELECT `司機id`, COUNT( `乘客id` ) AS `乘客數` FROM `訂單資料` GROUP BY `司機id` HAVING COUNT( `乘客id` ) > 1 ) AS a
    LEFT JOIN ( SELECT `司機id`, `城市id` FROM `司機資料` ) AS b ON a.`司機id` = b.`司機id`
    JOIN `城市比對資料` AS c ON b.`城市id` = c.`城市id`

滴滴SQL面試題之打車業務問題如何分析

一、題目

二、步驟

1、資料類型轉化

2、提取2020年8月各城市每天的司機數，快車訂單量和快車流水資料

①2020年8月各城市每天的司機數

②快車訂單量和快車流水資料

③小結

3、提取2020年8月和9月，每個月的北京市新老司機（首單日期在當月為新司機）的司機數、線上時長和TPH（訂單量/線上時長）資料。

①2020年8月和9月的新司機的司機數

②線上時長

③訂單量

④總結

4、分别提取司機數大于20，司機總線上時長大于2小時，訂單量大于1，乘客數大于1的城市名稱資料。

①司機數大于20的城市名稱

②司機線上總時長大于2小時

③ 訂單量大于1

④乘客數大于1

⑤小結

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述

滴滴SQL面試題之打車業務問題如何分析

一、題目

二、步驟

1、資料類型轉化

2、提取2020年8月各城市每天的司機數，快車訂單量和快車流水資料

①2020年8月各城市每天的司機數

②快車訂單量和快車流水資料

③小結

3、 提取2020年8月和9月，每個月的北京市新老司機（首單日期在當月為新司機）的司機數、線上時長和TPH（訂單量/線上時長）資料。

①2020年8月和9月的新司機的司機數

②線上時長

③訂單量

④總結

4、分别提取司機數大于20，司機總線上時長大于2小時，訂單量大于1，乘客數大于1的城市名稱資料。

①司機數大于20的城市名稱

②司機線上總時長大于2小時

③ 訂單量大于1

④乘客數大于1

⑤小結

繼續閱讀

3、提取2020年8月和9月，每個月的北京市新老司機（首單日期在當月為新司機）的司機數、線上時長和TPH（訂單量/線上時長）資料。