时间戳

首页 > 解决方案 > 查询每周或周末至少租过一次电影的客户

mysql - 查询每周或周末至少租过一次电影的客户

问题描述

我有一个电影租赁数据库。我拥有的表格适用于：

  1. 客户等级：

    • 主键：Customer_id(INT)
    • 名字（VARCHAR）
    • 姓氏（VARCHAR）

  2. 电影级别：

    • 主键：Film_id(INT)
    • 标题（VARCHAR）
    • 类别（VARCHAR）

  3. 出租等级：

    • 主键：Rental_id(INT)。

    此表中的其他列是：

    • Rental_date(DATETIME)
    • customer_id(INT)
    • 电影ID（INT）
    • 付款日期（日期时间）
    • 金额（十进制（5,2））

现在的问题是创建一个按以下分类的客户主列表：

我不是在这里寻找代码，而是在寻找解决这个问题的逻辑。尝试了很多方法，但无法形成关于如何每周查找客户 ID 的逻辑。我试过的代码如下：

select
   r.customer_id
 , concat(c.first_name, ' ', c.last_name) as Customer_Name
 , dayname(r.rental_date) as day_of_rental
 , case
     when dayname(r.rental_date) in ('Monday','Tuesday','Wednesday','Thursday','Friday')
     then 'Regulars'
     else 'Weekenders'
   end as Customer_Category
from rental r
inner join customer c on r.customer_id = c.customer_id;

我知道这是不正确的，但我无法超越这一点。

标签： mysqlsqljoin

解决方案

这是一个队列研究。首先找到每个组的最小表达式：

# Weekday regulars
SELECT
   customer_id
FROM rental
WHERE WEEKDAY(`date`) < 5 # 0-4 are weekdays

# Weekend warriors
SELECT
   customer_id
FROM rental
WHERE WEEKDAY(`date`) > 4 # 5 and 6 are weekends

现在我们知道如何获取在工作日和周末（包括在内）租用的客户列表。这些查询实际上只是告诉我们这些是在给定系列中的某一天访问过的客户，因此我们需要做出一些判断。

让我们引入一个周期性，它可以让我们获得阈值。我们也需要聚合，因此我们将通过分组到rental.customer_id.

# Weekday regulars
SELECT
   customer_id
 , COUNT(DISTINCT YEARWEEK(`date`)) AS weeks_as_customer
FROM rental
WHERE WEEKDAY(`date`) < 5
GROUP BY customer_id

# Weekend warriors
SELECT
   customer_id
 , COUNT(DISTINCT YEARWEEK(`date`)) AS weeks_as_customer
FROM rental
WHERE WEEKDAY(`date`) > 4 
GROUP BY customer_id

我们还需要一个行列式周期：

FLOOR(DATEDIFF(DATE(NOW()), '2019-01-01') / 7) AS weeks_in_period

把它们放在一起：

# Weekday regulars
SELECT
   customer_id
 , period.total_weeks
 , COUNT(DISTINCT YEARWEEK(`date`)) AS weeks_as_customer
FROM rental
WHERE WEEKDAY(`date`) < 5
CROSS JOIN (
  SELECT FLOOR(DATEDIFF(DATE(NOW()), '2019-01-01') / 7) AS total_weeks
) AS period
GROUP BY customer_id

# Weekend warriors
SELECT
   customer_id
 , period.total_weeks
 , COUNT(DISTINCT YEARWEEK(`date`)) AS weeks_as_customer
FROM rental
CROSS JOIN (
  SELECT FLOOR(DATEDIFF(DATE(NOW()), '2019-01-01') / 7) AS total_weeks
) AS period
WHERE WEEKDAY(`date`) > 4 
GROUP BY customer_id

所以现在我们可以介绍每个队列的阈值累加器。

# Weekday regulars
SELECT
   customer_id
 , period.total_weeks
 , COUNT(DISTINCT YEARWEEK(`date`)) AS weeks_as_customer
FROM rental
WHERE WEEKDAY(`date`) < 5
CROSS JOIN (
  SELECT FLOOR(DATEDIFF(DATE(NOW()), '2019-01-01') / 7) AS total_weeks
) AS period
GROUP BY customer_id
HAVING total_weeks = weeks_as_customer

# Weekend warriors
SELECT
   customer_id
 , period.total_weeks
 , COUNT(DISTINCT YEARWEEK(`date`)) AS weeks_as_customer
FROM rental
CROSS JOIN (
  SELECT FLOOR(DATEDIFF(DATE(NOW()), '2019-01-01') / 7) AS total_weeks
) AS period
WHERE WEEKDAY(`date`) > 4 
GROUP BY customer_id
HAVING total_weeks = weeks_as_customer

然后我们可以使用这些子查询我们的主列表。

SELECT
   customer.customer_id
 , CONCAT(customer.first_name, ' ', customer.last_name) as customer_name
 , CASE
     WHEN regulars.customer_id IS NOT NULL THEN 'regular'
     WHEN weekenders.customer_id IS NOT NULL THEN 'weekender'
     ELSE NULL
   AS category
FROM customer
CROSS JOIN (
  SELECT FLOOR(DATEDIFF(DATE(NOW()), '2019-01-01') / 7) AS total_weeks
) AS period
LEFT JOIN (
  SELECT
     rental.customer_id
   , period.total_weeks
   , COUNT(DISTINCT YEARWEEK(rental.`date`)) AS weeks_as_customer
  FROM rental
  WHERE WEEKDAY(rental.`date`) < 5
  GROUP BY rental.customer_id
  HAVING total_weeks = weeks_as_customer
) AS regulars ON customer.customer_id = regulars.customer_id
LEFT JOIN (
  SELECT
     rental.customer_id
   , period.total_weeks
   , COUNT(DISTINCT YEARWEEK(rental.`date`)) AS weeks_as_customer
  FROM rental
  WHERE WEEKDAY(rental.`date`) > 4
  GROUP BY rental.customer_id
  HAVING total_weeks = weeks_as_customer
) AS weekenders ON customer.customer_id = weekenders.customer_id
HAVING category IS NOT NULL

至于是否要排除跨队列（例如，因为周末租用而错过一周的常客——至少一次），存在一些歧义。您需要解决这种类型的包容性/排他性问题。

这将涉及回到特定于群组的查询以引入和调整查询以解释进一步理解的程度，和/或添加其他群组横切子查询，这些子查询可以以其他方式组合以建立更好和/或更多的理解在顶视图。

但是，鉴于此警告，我认为我提供的内容与您提供的内容合理匹配。

推荐阅读