时间戳

首页 > 解决方案 > 检查客户是否在表格中出现多次

mysql - 检查客户是否在表格中出现多次

问题描述

我有一张每天都会更新插入时间的表格。现在我想检查一个客户是出现在两个插入时间中还是只出现在其中一个中，并相应地标记它们。

insert-time     Customer           customer-appereance
2019-08-17      Customer01           each-insert-time
2019-08-18      Customer01           each-insert-time
2019-08-17      Customer02           first-list
2019-08-17      Customer03           each-insert-time
2019-08-18      Customer03           each-insert-time
2019-08-17      Customer04           first-list
2019-08-18      Customer05           last-list

我的数据库如下所示：

insert-time    customer
2019-08-17     Customer01
2019-08-17     Customer02
2019-08-17     Customer03
2019-08-17     Customer04
2019-08-18     Customer01
2019-08-18     Customer03
2019-08-18     Customer05

我试过的查询是这样的：

SELECT insert_time, customer,
(CASE WHEN MIN(insert_time) over (PARTITION by customer) = MAX(insert_time) over (PARTITION by customer)
THEN 'first-list' ELSE 'last-list' END) AS customer-appereance
FROM customers
GROUP BY 1,2;

不知何故，它不起作用。我也想知道如何将第三个cirtiera“每次插入时间”放入 CASE 语句中？

标签： mysql

解决方案

WITH 
cte1 AS (SELECT customer, MIN(`insert-time`) mindate, MAX(`insert-time`) maxdate
         FROM customers
         GROUP BY customer),
cte2 AS (SELECT MIN(`insert-time`) mindate, MAX(`insert-time`) maxdate
         FROM customers)
SELECT customers.`insert-time`,
       customers.customer,
       CASE WHEN cte1.mindate = cte2.maxdate THEN 'last-list'
            WHEN cte2.mindate = cte1.maxdate THEN 'first-list'
            ELSE 'each-insert-time'
            END `customer-appereance`
FROM customers
JOIN cte1 USING (customer)
CROSS JOIN cte2
ORDER BY 2,1;

或者

SELECT *,
       CASE WHEN MIN(`insert-time`) OVER (PARTITION BY customer) = MAX(`insert-time`) OVER ()
            THEN 'last-list'
            WHEN MAX(`insert-time`) OVER (PARTITION BY customer) = MIN(`insert-time`) OVER ()
            THEN 'first-list'
            ELSE 'each-insert-time'
            END `customer-appereance`
FROM customers
ORDER BY 2,1;

小提琴

推荐阅读