Mam tabelę, która przechowuje ilość błędów według identyfikatora alarmu. Tabela wygląda mniej więcej tak:

|----DATE----|---ALARM_ID---|---COUNTER---|
| 2012-01-01 |      1       |      32     |
| 2012-01-01 |      2       |      28     |
| 2012-01-02 |      1       |      12     |
| 2012-01-02 |      2       |      23     |
| 2012-01-03 |      1       |      3      |
| 2012-01-03 |      2       |      9      |
| 2012-01-05 |      1       |      8      |
| 2012-01-05 |      2       |      1      |
| 2012-01-07 |      1       |      102    |
| 2012-01-07 |      2       |      78     |

Zwróć uwagę na różnicę między datą (2012-01-03 - 2012-01-05) a (2012-01-05 - 2012-01-07). W tych terminach nie ma żadnych danych, ponieważ system, który monitoruje mój program, nie zgłosił w tym czasie żadnych błędów. To, czego szukam, to zapytanie SQL SELECT, które zwraca całkowitą liczbę błędów w każdym dniu, na przykład:

|----DATE----|---COUNTER---|
| 2012-01-01 |      60     |
| 2012-01-02 |      35     |
| 2012-01-03 |      12     |
| 2012-01-04 |      0      |
| 2012-01-05 |      9      |
| 2012-01-06 |      0      |
| 2012-01-07 |      180    |

Mam zapytanie, które zwraca identyfikatory, nawet jeśli nie istnieją w tabeli, a jeśli identyfikator nie istnieje, zwróć identyfikator mimo to z wartością COUNTER 0. W ten sposób:

        BEFORE                                     AFTER

|---ID---|---COUNTER---|                  |---ID---|---COUNTER---|
|   1    |      2      |                  |   1    |      2      |
|   2    |      6      |                  |   2    |      6      |
|   3    |      1      |       -->        |   3    |      1      |
|   5    |      9      |                  |   4    |      0      |
|   6    |      10     |                  |   5    |      9      |
                                          |   6    |      10     |
                                          |   7    |      0      |
                                          |   8    |      0      |

Zapytanie wygląda tak:

select t.num as ID, coalesce(yt.COUNTER, 0)
from all_stats yt right join 
( select t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 as num 
from ( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t1 cross join 
( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t2 cross join 
( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t3 cross join 
( select 1 as num union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 0 ) t4 ) 
t on yt.ID = t.num 
where (t.num between (select min(ID) from all_stats) and (select max(ID) from all_stats)) order by ID

Nie wiem, jak mogę zmienić to zapytanie, gdy dotyczy ono dat. Czy ktoś może mi pomóc w tej sprawie?

Używam MySQL.

Z góry dziękuję, Steve-O

1
ClydeFrog 10 luty 2012, 17:06

3 odpowiedzi

Najlepsza odpowiedź

Dokładne szczegóły będą zależeć od DBMS i charakteru bazy danych (np. zorientowana na OLAP vs. zorientowana na OLTP), ale jednym powszechnym podejściem ogólnym jest utworzenie pomocniczej tabeli calendar, która reprezentuje daty jako wymiar. Następnie możesz użyć zwykłych JOIN, zamiast używać złożonej logiki do generowania brakujących dat.

Odpowiedzi na to pytanie dotyczące StackOverflow opisują, jak zastosować to podejście w MySQL .

Nawiasem mówiąc, możesz użyć podobnego podejścia do liczb, mając tabele numbers; Nigdy nie robiłem tego dla liczb, ale wydaje się, że jest to popularny pomysł; zobacz to pytanie dba.stackexchange.com.

4
Community 23 maj 2017, 15:32

Jeśli używasz SQL Server 2005 lub nowszego, możesz użyć CTE (jeśli nie, pętli lub innej techniki sql do wypełnienia tabeli datami z zakresu). Należy również zauważyć, że istnieje limit poziomów rekurencji w ramach CTE.

declare @dateRange table
(
  dateBegin datetime,
  dateEnd datetime
)

insert into @dateRange (dateBegin, dateEnd) 
values ('2012-01-01', '2012-01-07')

;with cte (d)
as (select dateBegin as d
    from @dateRange tbl
    where datediff(day, tbl.dateBegin, tbl.dateEnd) <= 100
    union all
    select dateadd(day, 1, cte.d) as d
    from cte
      inner join @dateRange tbl on cte.d < tbl.dateEnd)

Następnie uzyskaj pełne wyniki za pomocą CTE lub tabeli tymczasowej zawierającej zestaw dat z zakresu:

select cte.d, sum(isnull(e.errorCounter, 0))
from cte
  left outer join @errors e on e.errorDate = cte.d
group by cte.d
order by cte.d
1
kaj 10 luty 2012, 22:40

Naprawdę powinieneś zająć się tym w warstwie aplikacji (tj. iterować w znanym zakresie dat i pobierać wartości niezerowe ze zbioru wyników) lub poprawić tabelę tak, aby zawsze zawierała potrzebne daty, jeśli MUSISZ mieć rozwiązanie skoncentrowane na bazie danych. Nie ma naprawdę dobrego sposobu na wygenerowanie w locie zestawu dat do wykorzystania w tworzeniu ciągłego zapytania zakresu dat.

Możesz to zobaczyć na przykładach rozwiązań skryptowych DB:

Tabela temp powrotu ciągłych dat

Ale myślę, że stawiasz złe pytanie. Popraw bazę danych, aby zawierała to, czego potrzebujesz, lub popraw sposób generowania raportu. Bazy danych nie służą do interpolacji i generowania danych.

0
Community 23 maj 2017, 14:54