时间戳

首页 > 解决方案 > MySQL 按日期查询逻辑

mysql - MySQL 按日期查询逻辑

问题描述

该地区的餐馆为顾客提供各种食物。只要顾客经常出现在一家餐厅，那家餐厅就会继续供应同样的菜肴。如果超过 3 天没有顾客光顾一家餐厅，那么该餐厅将在下一位顾客光顾时开始提供一种新型食物。不幸的是，一些成分被污染，导致一些顾客食物中毒。餐厅知道哪些食物受到影响，现在正试图找出哪些顾客受到影响。餐厅保留自己的顾客目录，因此不能保证顾客在每家餐厅都有相同的顾客 ID。所有餐厅都以相同的顺序提供食物。找出哪个顾客吃了哪种食物。

样本数据：

餐厅：

restaurant_id   customer_id visit_date
1   1   2020-01-01
2   1   2020-01-01
1   2   2020-01-03
3   1   2020-01-04
2   2   2020-01-14
3   1   2020-01-11
2   3   2020-01-14

食物：

food_id food_name
1   Spaghetti
2   Chicken and Rice
3   Tacos

期望：

Restaurant  Customer    Visit   Food
1   1   2020-01-01  Spaghetti
2   1   2020-01-01  Spaghetti
1   2   2020-01-03  Spaghetti
3   1   2020-01-04  Spaghetti
2   2   2020-01-10  Chicken and Rice
3   1   2020-01-11  Chicken and Rice
2   3   2020-01-14  Tacos

标签： mysqlsql

解决方案

Step1：添加一个行号以按日期顺序处理，并添加一列food_factor，其值为1表示不同的食物，0表示相同的食物

step2 : 为 row=1 分配 1 （因为客户访问的第一个日期提供食物 id=1）

step3：按照每家餐厅的访问日期顺序查找id的累积总和

step4：在 food_id 上左加入食物表以获取食物名称

SELECT restaurant_id, 
    customer_id, 
    visit_date, 
    f.food_name 
FROM ( 
      SELECT restaurant_id, 
            customer_id, 
            visit_date, 
            Sum(food_factor) OVER (partition BY restaurant_id ORDER BY row) AS foodid
      FROM ( 
             SELECT restaurant_id, 
              customer_id, 
              visit_date, 
              row, 
              CASE 
               WHEN row=1 THEN 1 
               ELSE food_factor 
              END AS food_factor 
             FROM ( 
                   SELECT restaurant_id, 
                    customer_id, 
                    visit_date, 
                    Row_number() OVER( partition BY restaurant_id ORDER BY visit_date) AS row
                    case 
                      WHEN datediff(day,visit_date , lag(visit_date)over(partition by restaurant_id ORDER BY visit_date) >3 THEN 1
                      ELSE 0 
                    END AS food_factor FROM restaurant
                    ) restaurant_food 
                  ) final 
            ) a
        LEFT OUTER JOIN food f 
        ON     a.foodid=f.food_id

推荐阅读