Блог

Главная - Вопросы по программированию - Группировка нескольких дат чтения в кластеры, N-е чтение этого года

Группировка нескольких дат чтения в кластеры, N-е чтение этого года

#mysql #sql #stored-procedures

#mysql #sql #хранимые процедуры

Вопрос:

  ------------ ------ 
| 2011-03-04 |    6 |
| 2011-03-01 |    1 |
| 2011-02-28 |    4 |
| 2011-02-24 |    1 |
| 2011-02-23 |    1 |
| 2011-02-22 |    2 |
| 2011-02-17 |    1 |
| 2011-02-16 |   22 |
| 2011-02-12 | 2033 |
| 2011-02-10 |    1 |
| 2011-02-07 |    1 |
| 2011-01-04 |    1 |
| 2011-01-03 |    5 |
| 2010-12-26 |    6 |
| 2010-12-16 |    1 |
| 2010-12-15 |  158 |
| 2010-12-14 | 1703 |
| 2010-12-13 |  199 |
| 2010-11-08 |    1 |
| 2010-10-28 |    3 |
| 2010-10-27 |    6 |
| 2010-10-25 |    1 |
| 2010-10-21 |  660 |
| 2010-10-20 |  558 |
| 2010-10-19 |  245 |
| 2010-10-18 |  579 |
| 2010-10-15 |   14 |
| 2010-10-14 |    1 |
| 2010-10-04 |    1 |
| 2010-09-08 |    1 |
| 2010-09-01 |    1 |
| 2010-08-31 |    1 |
| 2010-08-30 |    6 |
| 2010-08-26 |    1 |
| 2010-08-24 |    4 |
| 2010-08-23 |    2 |
| 2010-08-19 |    3 |
| 2010-08-18 |  144 |
| 2010-08-17 |  920 |
| 2010-08-16 |  982 |
| 2010-08-03 |    1 |
| 2010-08-02 |    1 |
| 2010-07-12 |    1 |
| 2010-06-30 |    8 |
| 2010-06-29 |    1 |
| 2010-06-28 |    1 |
| 2010-06-23 |    1 |
| 2010-06-22 |    1 |
| 2010-06-17 |    7 |
| 2010-06-16 |  703 |
| 2010-06-15 |  937 |
| 2010-06-14 |  397 |
| 2010-06-10 |    2 |
| 2010-06-09 |    1 |
| 2010-06-01 |    5 |
| 2010-05-26 |    1 |
| 2010-05-05 |    1 |
| 2010-04-27 |    2 |
| 2010-04-26 |    4 |
| 2010-04-24 |    6 |
| 2010-04-22 |    2 |
| 2010-04-21 |  351 |
| 2010-04-20 |  839 |
| 2010-04-19 |  850 |
| 2010-04-18 |    2 |
| 2010-04-15 |    2 |
| 2010-04-07 |    1 |
| 2010-04-01 |    2 |
| 2010-03-30 |    1 |
| 2010-03-22 |    1 |
| 2010-03-10 |    1 |
| 2010-03-08 |    1 |
| 2010-03-04 |    1 |
| 2010-03-01 |    3 |
| 2010-02-27 |    6 |
| 2010-02-25 |    2 |
| 2010-02-23 |    4 |
| 2010-02-22 |    1 |
| 2010-02-18 |  188 |
| 2010-02-17 | 1210 |
| 2010-02-16 |  646 |
| 2010-01-27 |    1 |
| 2010-01-21 |    1 |
| 2010-01-07 |    1 |
| 2010-01-06 |    2 |
| 2010-01-04 |   12 |
 ------------ ------

У меня есть этот набор данных за последние годы. Я хотел бы объединить похожие даты чтения в одну. Например, возьмите диапазоны 2011-02-07 и 2011-03-04 и объедините их вместе как номер чтения: 1 этого года.

Или объедините 2010-10-04 и 2010-10-28 как номер чтения: 5-е число этого года.

Группировки аналогичны на основе количества чтений, основанных на 2-м столбце. Есть пики, которые необходимо сгруппировать вместе. Это будет 6 периодов в каждом году и разница между ними не менее 40 дней.

Как я могу это сделать в MySQL?

Комментарии:

1. Вы имеете в виду, что хотите автоматически сопоставлять периоды всплесков или будете определять их вручную?

2. Я хочу автоматически сопоставлять всплески в 6 группах каждый год.

3. Я видел несколько примеров дат в вашем сообщении, но я не понял, что определило эти даты как точки отсечения. Можете ли вы уточнить?

4. Например, даты, относящиеся к 2011-02-12 2033 году, группируются вместе с номером чтения 1 года. Даты около 2010-04-19 с 850 сгруппированы вместе как чтение под номером 2 за этот год. Даты между пиками должны быть сгруппированы по ближайшим пикам.

5. Хорошо, даты отсечения кажутся теперь четко определенными. Теперь что насчет пиков? Как они должны быть обнаружены? Или это на самом деле вопрос?

Ответ №1:

Я взял ваши примерные данные и поместил их в простую таблицу:

 CREATE TABLE `usage_bill` (
  `readdate` date default NULL,
  `reading` int(11) default NULL
);

Я дошел до того, что смог определить пики общим способом, подобным этому:

 SET @seq1 := 0;
SET @seq2 := 0;

SET @lastdiff := 0;

SELECT readdate, reading FROM  (
    SELECT ref2.readdate, ref1.reading, ref2.reading - ref1.reading AS diff,
        (@lastdiff>0) amp;amp; (ref2.reading - ref1.reading)<0 AS peak,
            @lastdiff := ref2.reading - ref1.reading AS lastdiff FROM
        (SELECT @seq1 := @seq1   1 AS rowNum, readdate, reading FROM usage_bill ORDER BY readdate) AS ref1,
        (SELECT @seq2 := @seq2   1 AS rowNum, readdate, reading FROM usage_bill ORDER BY readdate) AS ref2
        WHERE ref1.rowNum 1 = ref2.rowNum ) AS peaks
WHERE peak=1;

Теоретически должно быть возможно просто добавить ORDER BY reading DESC LIMIT 6 , чтобы получить самые большие пики, но на практике это не так, поскольку не все пики являются чистыми кривыми (например, октябрь 2010).

Не уверен, поможет ли это вам вообще…

Ответ №2:

Мне удалось заставить это работать с помощью пользовательских переменных, но с этим есть небольшая проблема.

 SET @stepdate:=DATE('1970-01-01'); 

SET @reading:=0; 

SET @prevdate:=DATE('1970-01-01'); 

SELECT readdate, 
       IF(( To_days(`readdate`) - To_days(@stepdate) ) > 40 
           OR (SELECT COUNT(*) 
               FROM   usage_bill 
               WHERE  readdate BETWEEN d.readdate AND DATE_ADD(d.readdate, 
                                                      INTERVAL 25 DAY 
                                                      ) > 
                      100), @reading := @reading   1, @reading) AS rownum, 
       IF(( To_days(`readdate`) - To_days(@stepdate) ) > 40 
           OR (SELECT COUNT(*) 
               FROM   usage_bill 
               WHERE  readdate BETWEEN d.readdate AND DATE_ADD(d.readdate, 
                                                      INTERVAL 25 DAY 
                                                      ) > 
                      100), @stepdate := readdate, @stepdate)   AS dd, 
       IF(YEAR(@stepdate)<>YEAR(@prevdate), @reading := 1 XOR 
                                            @prevdate := @stepdate, 
       @reading), 
       @reading                                                 AS nthgroup 
FROM   (SELECT `readdate`, 
               COUNT(*) c 
        FROM   `usage_bill` 
        GROUP  BY `readdate` 
        HAVING COUNT(*) > 20 
        ORDER  BY `usage_bill`.`readdate` ASC) d

Проблема в том, что когда я удаляю HAVING COUNT(*) > 20 , я получаю даты чтения, которые не помещаются между датами чтения, и составляю новую группу.