时间戳

首页 > 解决方案 > 如何在mysql中进行队列分析

mysql - 如何在mysql中进行队列分析

问题描述

我有一张桌子叫order_star_member：

create table order_star_member(
   id INT UNSIGNED NOT NULL AUTO_INCREMENT,
   users_id INT(11) NOT NULL,
   createdAt datetime NOT NULL,
   total_price_star_member decimal(10,2) NOT NULL,
   PRIMARY KEY (id)
);

INSERT INTO order_star_member(users_id, createdAt, total_price_star_member)
VALUES
(15, '2021-01-01', 350000),
(15, '2021-01-02', 400000),
(16, '2021-01-02', 700000),
(15, '2021-02-01', 350000),
(16, '2021-02-02', 700000),
(15, '2021-03-01', 350000),
(16, '2021-03-01', 850000),
(17, '2021-03-03', 350000);

DB小提琴

transaction >= 700000我想用和 查找三月份的用户first transaction >= 700000。>= 700000调用其事务的用户star member。

到目前为止我的查询：

    SELECT COUNT(users_id) count_star_member,
           year_and_month DIV 100 `year`,
           year_and_month MOD 100 `month`
    FROM (SELECT users_id, 
                 MIN(year_and_month) year_and_month
          FROM ( SELECT users_id, 
                        DATE_FORMAT(createdAt, '%Y%m') year_and_month,
                        SUM(total_price_star_member) month_price
                 FROM order_star_member
                 GROUP BY users_id, 
                          DATE_FORMAT(createdAt, '%Y%m') 
                 HAVING month_price >= 350000 ) starrings
          GROUP BY users_id
          HAVING SUM(year_and_month = '202103') > 0 ) first_starrings
    GROUP BY year_and_month
    ORDER BY `year`, `month`;

    +-------------------+------+-------+
    | count_star_member | year | month |
    +-------------------+------+-------+
    |                 1 | 2021 |     1 |
    +-------------------+------+-------+

说明：2021年3月只有一个“明星会员”，即users_id 16，其第一笔交易在january 2021，所以2021年3月的“明星会员”如上。

但从3月份开始，“明星会员”的定义从70万变成了35万。

我想找到3月份的“明星会员”，以及他的第一笔交易，但是如果第一笔交易是在2021年3月之前的一个月，那么明星会员应该是交易> = 700,000的用户-但如果第一笔交易是在 2021 年 3 月，正如我 sid，选择一个交易 >= 350,000 的用户。

因此我更新的期望：

        +-------------------+------+-------+
        | count_star_member | year | month |
        +-------------------+------+-------+
        |                 2 | 2021 |     1 |
        |                 1 | 2021 |     3 |
        +-------------------+------+-------+

说明：用户15、16、17是2021年3月的明星会员，但15、16号用户是2021年1月做第一个明星会员（因为是2021年3月之前，当明星会员的要求是70万），而用户 17 也是明星会员，因为 2021 年 3 月的第一笔交易是 350,000。

标签： mysqlsql

解决方案

这可能是您需要的：

SELECT min_year, min_month, COUNT(users_id)
FROM (
    SELECT osm2.users_id, YEAR(min_createdAt) min_year, MONTH(min_createdAt) min_month, SUM(total_price_star_member) sum_price
    FROM (
        SELECT users_id, MIN(createdAt) min_createdAt       
        FROM order_star_member
        GROUP BY users_id
    ) AS osm1
    JOIN order_star_member osm2 ON osm1.users_id = osm2.users_id
    WHERE DATE_FORMAT(osm2.createdAt, '%Y%m') = DATE_FORMAT(osm1.min_createdAt, '%Y%m')
    GROUP BY osm2.users_id, min_createdAt
) t1
WHERE users_id IN (
    SELECT users_id
    FROM (
        SELECT users_id, DATE_FORMAT(createdAt, '%Y-%m-01') month_createdAt
        FROM order_star_member
        WHERE DATE_FORMAT(createdAt, '%Y%m') = '202103'
        GROUP BY users_id, DATE_FORMAT(createdAt, '%Y-%m-01')
        HAVING SUM(total_price_star_member) >= (
            CASE
                WHEN date(month_createdAt) < date '2021-03-01' THEN 700000
                ELSE 350000
            END
        )
    ) t3
) AND       
    (((min_year < 2021 OR min_month < 3) AND t1.sum_price >= 700000) OR 
    ((min_year = 2021 AND min_month = 3) AND t1.sum_price >= 350000))
GROUP BY min_year, min_month

首先，您找到MIN(createdAt)每个成员的，其中：

SELECT users_id, MIN(createdAt) min_createdAt       
FROM order_star_member
GROUP BY users_id

然后你计算日期当月的SUM所有：total_price_star_membermin_createdAt

SELECT osm2.users_id, YEAR(min_createdAt) min_year, MONTH(min_createdAt) min_month, SUM(total_price_star_member) sum_price
FROM osm1
JOIN order_star_member osm2 ON osm1.users_id = osm2.users_id
WHERE DATE_FORMAT(osm2.createdAt, '%Y%m') = DATE_FORMAT(osm1.min_createdAt, '%Y%m')
GROUP BY osm2.users_id, min_createdAt

接下来，您过滤您感兴趣的月份。在这里，您不能使用HAVING无法从语句中计算的内容GROUP BY，因此您还需要预测以在现在的子句中DATE_FORMAT(createdAt, '%Y-%m-01')建立星级会员的最低总价HAVING允许。

SELECT users_id
FROM (
    SELECT users_id, DATE_FORMAT(createdAt, '%Y-%m-01') month_createdAt
    FROM order_star_member
    WHERE DATE_FORMAT(createdAt, '%Y%m') = '202102'
    GROUP BY users_id, DATE_FORMAT(createdAt, '%Y-%m-01')
    HAVING SUM(total_price_star_member) >= (
        CASE
            WHEN date(month_createdAt) < date '2021-03-01' THEN 700000
            ELSE 350000
        END
    )
) t3

最后，您还要检查min_monthand min_year，然后根据这些属性以及COUNT每个组中有多少成员进行分组。

SELECT min_year, min_month, COUNT(users_id)
FROM t1
WHERE users_id IN (...) AND     
    (((min_year < 2021 OR min_month < 3) AND t1.sum_price >= 700000) OR 
    ((min_year = 2021 AND min_month = 3) AND t1.sum_price >= 350000))
GROUP BY min_year, min_month

我没有立即理解你的目标是什么，我不确定我现在明白了，这就是为什么我现在改变了这个查询几次，所以你可以简化它。

推荐阅读