Medium之1321.餐馆营业额变化增长***

偏执的太偏执、 2022-08-30 04:08 97阅读 0赞

**表: Customer**

<table> 
 <thead> 
  <tr> 
   <th>Column Name</th> 
   <th>Type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>customer_id</td> 
   <td>int</td> 
  </tr> 
  <tr> 
   <td>name</td> 
   <td>varchar</td> 
  </tr> 
  <tr> 
   <td>visited_on</td> 
   <td>date</td> 
  </tr> 
  <tr> 
   <td>amount</td> 
   <td>int</td> 
  </tr> 
 </tbody> 
</table>

(customer\_id, visited\_on) 是该表的主键  
该表包含一家餐馆的顾客交易数据  
visited\_on 表示 (customer\_id) 的顾客在 visited\_on 那天访问了餐馆  
amount 是一个顾客某一天的消费总额

### 问题 ###

你是餐馆的老板，现在你想分析一下可能的营业额变化增长（每天至少有一位顾客）

写一条 SQL 查询计算以 7 天（某日期 + 该日期前的 6 天）为一个时间段的顾客消费平均值  
查询结果按 visited\_on 排序  
average\_amount 要 保留两位小数，日期数据的格式为 (‘YYYY-MM-DD’)

### 示例 ###

**Customer 表:**

<table> 
 <thead> 
  <tr> 
   <th>customer_id</th> 
   <th>name</th> 
   <th>visited_on</th> 
   <th>amount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Jhon</td> 
   <td>2019-01-01</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Daniel</td> 
   <td>2019-01-02</td> 
   <td>110</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Jade</td> 
   <td>2019-01-03</td> 
   <td>120</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Khaled</td> 
   <td>2019-01-04</td> 
   <td>130</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Winston</td> 
   <td>2019-01-05</td> 
   <td>110</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Elvis</td> 
   <td>2019-01-06</td> 
   <td>140</td> 
  </tr> 
  <tr> 
   <td>7</td> 
   <td>Anna</td> 
   <td>2019-01-07</td> 
   <td>150</td> 
  </tr> 
  <tr> 
   <td>8</td> 
   <td>Maria</td> 
   <td>2019-01-08</td> 
   <td>80</td> 
  </tr> 
  <tr> 
   <td>9</td> 
   <td>Jaze</td> 
   <td>2019-01-09</td> 
   <td>110</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>Jhon</td> 
   <td>2019-01-10</td> 
   <td>130</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Jade</td> 
   <td>2019-01-10</td> 
   <td>150</td> 
  </tr> 
 </tbody> 
</table>

**Result表:**

<table> 
 <thead> 
  <tr> 
   <th>visited_on</th> 
   <th>amount</th> 
   <th>average_amount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2019-01-07</td> 
   <td>860</td> 
   <td>122.86</td> 
  </tr> 
  <tr> 
   <td>2019-01-08</td> 
   <td>840</td> 
   <td>120</td> 
  </tr> 
  <tr> 
   <td>2019-01-09</td> 
   <td>840</td> 
   <td>120</td> 
  </tr> 
  <tr> 
   <td>2019-01-10</td> 
   <td>1000</td> 
   <td>142.86</td> 
  </tr> 
 </tbody> 
</table>

第一个七天消费平均值从 2019-01-01 到 2019-01-07 是 (100 + 110 + 120 + 130 + 110 + 140 + 150)/7 = 122.86  
第二个七天消费平均值从 2019-01-02 到 2019-01-08 是 (110 + 120 + 130 + 110 + 140 + 150 + 80)/7 = 120  
第三个七天消费平均值从 2019-01-03 到 2019-01-09 是 (120 + 130 + 110 + 140 + 150 + 80 + 110)/7 = 120  
第四个七天消费平均值从 2019-01-04 到 2019-01-10 是 (130 + 110 + 140 + 150 + 80 + 110 + 130 + 150)/7 = 142.86

来源：力扣（LeetCode）  
链接：https://leetcode-cn.com/problems/restaurant-growth  
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

### 解答 ###

SELECT visited_on, amount, round(average_amount,2) average_amount
    FROM
        (SELECT visited_on, 
        SUM(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING)  amount, 
        AVG(amount) OVER (ORDER BY visited_on ROWS 6 PRECEDING) AS average_amount,
        ROW_NUMBER() OVER (ORDER BY visited_on) AS rn
        FROM
            (SELECT DISTINCT visited_on, 
                    SUM(amount) over (PARTITION BY visited_on ORDER BY visited_on) AS amount
            FROM Customer) a  #有partition by visited_on，是考虑会有一天有多条消费记录
         ) b
    WHERE b.rn >= 7  #从而找到符合条件的visited_on

##### 知识点 #####

1.ROWS 6 PRECEDING等价于rows between 6 proceding and current row  
:表示取当前行和前六行；

2.WHERE b.rn >= 7  
通过窗口函数，从而找到符合条件的visited\_on  
或者  
不用ROW\_NUMBER() OVER (ORDER BY visited\_on) AS rn  
where datediff(visited\_on,(select min(visited\_on)from Customer))>=6。