Блог

У меня есть столик под названием 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);
 

Скрипка БД

Я хочу найти пользователей в марте месяце с 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 года есть только один «звездный участник», то есть users_id 16 , чья первая транзакция january 2021 , поэтому «звездный участник» в марте 2021 года, как указано выше.

Но начиная с марта определение «звездный участник» меняется с 700 000 до 350 000.

Я хочу найти «звездного участника» в марте и его первую транзакцию, но если первая транзакция состоится за месяц до марта 2021 года, то звездным участником должен быть пользователь, транзакция которого >= 700 000, но если первая транзакция состоится в марте 2021 года, как я указываю, выберите пользователя, транзакция которого >>= 350 000.

Таким образом, мои обновленные ожидания:

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

Пояснение : пользователи 15, 16 и 17 являются участниками star в марте 2021 года. но пользователи 15 и 16 становятся первыми участниками star в январе 2021 года (потому что это до марта 2021 года, когда требование стать участником star составляет 700 000), в то время как пользователь 17 также является участником star, потому что первая транзакция составляет 350 000 в марте 2021 года.

Я понимаю, что при определении конечного результата вам нужно 2 вещи:

1. Первая транзакция пользователя
2. Пользователи, которые являются звездными участниками в течение запрошенного месяца, используют условие, что до марта 2021 года совокупные суммы ежемесячных транзакций >=700000, а после марта >>=350000

Если это правильно, так как вы используете версию менее 8.0(где это можно было бы сделать с помощью одного оператора), ваше решение выглядит следующим образом:

1. Вам нужна таблица правил или некоторая конфигурация правил (мы назовем ее SMLimitDef), которая выглядела бы так, как если бы она была введена непосредственно в таблицу:

 insert into SMLimitDef(sEffDate,eEffDate,priceLimit) 
VALUES('1980-01-01','2021-02-28',700000),
('2021-03-01','2999-12-31',350000);

 

2. Далее вам нужен запрос или представление, которое определяет ваши первые транзакции(называемые vFirstUserTransMatch), которые будут выглядеть примерно так:

 create view vFirstUserTransMatch as
SELECT *,month(osm.createdAt) as createMonth, year(osm.createdAt) as createYear 
FROM order_star_member osm
where createdAt=(select MIN(createdAt) from order_star_member b 
                  where b.users_id=osm.users_id
                 )
 

3. Затем вам понадобится сводное представление или запрос, в котором суммируются транзакции за месяц для каждого пользователя

 create view vOSMSummary as
SELECT users_id,month(osm.createdAt) as createMonth, year(osm.createdAt) as createYear, sum(total_price_star_member) as totalPrice 
FROM order_star_member osm
group by users_id,month(osm.createdAt), year(osm.createdAt);
 

4. Далее вам нужен запрос, который объединяет все это в соответствии с вашими критериями:

 select osm.*,futm.createMonth as firstMonth, futm.createYear as firstYear 
from vOSMSummary osm 
inner join vFirstUserTransMatch futm
on osm.users_id=futm.users_id
where exists(select 'x' from SMLimitDef c 
             where osm.createMonth between Month(c.sEffDate) and Month(c.eEffDate)
             and osm.createYear between Year(c.sEffDate) and Year(c.eEffDate)
             and osm.totalPrice>=c.pricelimit
            )
and osm.CreateMonth=3 and osm.createYear=2021
 

5. Наконец, вы можете сделать свое резюме

 SELECT COUNT(users_id) count_star_member,
       firstYear `year`,
       firstMonth `month`
FROM (
select osm.*,futm.createMonth as firstMonth, futm.createYear as firstYear 
from vOSMSummary osm 
inner join vFirstUserTransMatch futm
on osm.users_id=futm.users_id
where exists(select 'x' from SMLimitDef c 
             where osm.createMonth between Month(c.sEffDate) and Month(c.eEffDate)
             and osm.createYear between Year(c.sEffDate) and Year(c.eEffDate)
             and osm.totalPrice>=c.pricelimit
            )
and osm.CreateMonth=3 and osm.createYear=2021
) d
group by firstYear, firstMonth
 

Как я уже сказал, если бы вы использовали MySQL 8, все могло бы быть в одном запросе с использованием операторов «С», но для вашей версии, для удобства чтения и простоты, вам нужны представления, иначе вы все равно можете встроить sql для этих представлений в окончательный sql.

Скрипка выглядит так

Контрастирует с версией 8, которая выглядит так

Это, вероятно, то, что вам нужно:

 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_member в месяце min_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') , чтобы установить минимальную общую цену за членство в star в 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_month и 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
 

Я не сразу понял, какова ваша цель, и я не уверен, что понимаю ее сейчас, поэтому я уже несколько раз менял этот запрос, чтобы вы могли упростить его.