Buduję bazę danych w Postgres, gdzie będzie dużo grupowania rzeczy według monthi year, ale nigdy przez date.

• Mógłbym utworzyć liczby całkowite monthi yearkolumny i użyć ich.
• Lub mógłbym mieć month_yearkolumnę i zawsze ustawić na day1.

Ten pierwszy wydaje się nieco prostszy i jaśniejszy, jeśli ktoś patrzy na dane, ale drugi jest fajny, ponieważ używa odpowiedniego typu.

Osobiście, jeśli jest to data lub może to być data, sugeruję, aby zawsze przechowywać ją jako jedną. Zasadniczo łatwiej jest pracować.

• Data to 4 bajty.
• Smallint ma 2 bajty (potrzebujemy dwóch)
  • ... 2 bajty: jeden smallint na rok
  • ... 2 bajty: jeden smallint na miesiąc

Możesz mieć jedną datę, która będzie wspierać dzień, jeśli kiedykolwiek będziesz jej potrzebować, lub jedną smallintna rok i miesiąc, która nigdy nie zapewni dodatkowej precyzji.

Przykładowe dane

Spójrzmy teraz na przykład. Utwórzmy milion dat dla naszej próbki. To około 5000 wierszy na 200 lat między 1901 a 2100. Każdego roku powinno być coś na każdy miesiąc.

CREATE TABLE foo
AS
  SELECT
    x,
    make_date(year,month,1)::date AS date,
    year::smallint,
    month::smallint
  FROM generate_series(1,1e6) AS gs(x)
  CROSS JOIN LATERAL CAST(trunc(random()*12+1+x-x) AS int) AS month
  CROSS JOIN LATERAL CAST(trunc(random()*200+1901+x-x) AS int) AS year
;
CREATE INDEX ON foo(date);
CREATE INDEX ON foo (year,month);
VACUUM FULL ANALYZE foo;

Testowanie

Prosty WHERE

Teraz możemy przetestować teorie nieużywania daty. Każdą z nich przeprowadziłem kilka razy, aby rozgrzać.

EXPLAIN ANALYZE SELECT * FROM foo WHERE date = '2014-1-1'
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=11.56..1265.16 rows=405 width=14) (actual time=0.164..0.751 rows=454 loops=1)
   Recheck Cond: (date = '2014-04-01'::date)
   Heap Blocks: exact=439
   ->  Bitmap Index Scan on foo_date_idx  (cost=0.00..11.46 rows=405 width=0) (actual time=0.090..0.090 rows=454 loops=1)
         Index Cond: (date = '2014-04-01'::date)
 Planning time: 0.090 ms
 Execution time: 0.795 ms

Teraz wypróbujmy inną metodę z osobnymi

EXPLAIN ANALYZE SELECT * FROM foo WHERE year = 2014 AND month = 1;
                                                           QUERY PLAN                                                           
--------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=12.75..1312.06 rows=422 width=14) (actual time=0.139..0.707 rows=379 loops=1)
   Recheck Cond: ((year = 2014) AND (month = 1))
   Heap Blocks: exact=362
   ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.64 rows=422 width=0) (actual time=0.079..0.079 rows=379 loops=1)
         Index Cond: ((year = 2014) AND (month = 1))
 Planning time: 0.086 ms
 Execution time: 0.749 ms
(7 rows)

Szczerze mówiąc, nie wszystkie są 0,749 .. niektóre są trochę mniej więcej, ale to nie ma znaczenia. Wszystkie są względnie takie same. To po prostu nie jest potrzebne.

W przeciągu jednego miesiąca

Teraz bawmy się dobrze. Załóżmy, że chcesz znaleźć wszystkie interwały w ciągu 1 miesiąca od stycznia 2014 r. (Tego samego miesiąca, którego użyliśmy powyżej).

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE date
    BETWEEN
      ('2014-1-1'::date - '1 month'::interval)::date 
      AND ('2014-1-1'::date + '1 month'::interval)::date;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=21.27..2310.97 rows=863 width=14) (actual time=0.384..1.644 rows=1226 loops=1)
   Recheck Cond: ((date >= '2013-12-01'::date) AND (date <= '2014-02-01'::date))
   Heap Blocks: exact=1083
   ->  Bitmap Index Scan on foo_date_idx  (cost=0.00..21.06 rows=863 width=0) (actual time=0.208..0.208 rows=1226 loops=1)
         Index Cond: ((date >= '2013-12-01'::date) AND (date <= '2014-02-01'::date))
 Planning time: 0.104 ms
 Execution time: 1.727 ms
(7 rows)

Porównaj to z metodą łączoną

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE year = 2013 AND month = 12
    OR ( year = 2014 AND ( month = 1 OR month = 2) );

                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=38.79..2999.66 rows=1203 width=14) (actual time=0.664..2.291 rows=1226 loops=1)
   Recheck Cond: (((year = 2013) AND (month = 12)) OR (((year = 2014) AND (month = 1)) OR ((year = 2014) AND (month = 2))))
   Heap Blocks: exact=1083
   ->  BitmapOr  (cost=38.79..38.79 rows=1237 width=0) (actual time=0.479..0.479 rows=0 loops=1)
         ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.64 rows=421 width=0) (actual time=0.112..0.112 rows=402 loops=1)
               Index Cond: ((year = 2013) AND (month = 12))
         ->  BitmapOr  (cost=25.60..25.60 rows=816 width=0) (actual time=0.218..0.218 rows=0 loops=1)
               ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.62 rows=420 width=0) (actual time=0.108..0.108 rows=423 loops=1)
                     Index Cond: ((year = 2014) AND (month = 1))
               ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..12.38 rows=395 width=0) (actual time=0.108..0.108 rows=401 loops=1)
                     Index Cond: ((year = 2014) AND (month = 2))
 Planning time: 0.256 ms
 Execution time: 2.421 ms
(13 rows)

Jest zarówno wolniejszy, jak i brzydszy.

GROUP BY/ORDER BY

Metoda łączona,

EXPLAIN ANALYZE
  SELECT date, count(*)
  FROM foo
  GROUP BY date
  ORDER BY date;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=20564.75..20570.75 rows=2400 width=4) (actual time=286.749..286.841 rows=2400 loops=1)
   Sort Key: date
   Sort Method: quicksort  Memory: 209kB
   ->  HashAggregate  (cost=20406.00..20430.00 rows=2400 width=4) (actual time=285.978..286.301 rows=2400 loops=1)
         Group Key: date
         ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.012..70.582 rows=1000000 loops=1)
 Planning time: 0.094 ms
 Execution time: 286.971 ms
(8 rows)

I znowu metodą kompozytową

EXPLAIN ANALYZE
  SELECT year, month, count(*)
  FROM foo
  GROUP BY year, month
  ORDER BY year, month;
                                                        QUERY PLAN                                                        
--------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23064.75..23070.75 rows=2400 width=4) (actual time=336.826..336.908 rows=2400 loops=1)
   Sort Key: year, month
   Sort Method: quicksort  Memory: 209kB
   ->  HashAggregate  (cost=22906.00..22930.00 rows=2400 width=4) (actual time=335.757..336.060 rows=2400 loops=1)
         Group Key: year, month
         ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=4) (actual time=0.010..70.468 rows=1000000 loops=1)
 Planning time: 0.098 ms
 Execution time: 337.027 ms
(8 rows)

Wniosek

Zasadniczo niech mądrzy ludzie wykonują ciężką pracę. Datemath jest trudny, moi klienci nie płacą mi wystarczająco. Robiłem te testy. Trudno mi było dojść do wniosku, że mogę uzyskać lepsze wyniki niż date. Przestałem próbować.

AKTUALIZACJE

@ Koń_nazwa_na_nazwy sugerowany do mojego testu w ciągu jednego miesiącaWHERE (year, month) between (2013, 12) and (2014,2) . Moim zdaniem, choć fajne, jest to bardziej złożone zapytanie i wolałbym go unikać, chyba że byłby to zysk. Niestety, było jeszcze wolniej, chociaż jest blisko - co jest bardziej oddalone od tego testu. To po prostu nie ma większego znaczenia.

EXPLAIN ANALYZE
  SELECT *
  FROM foo
  WHERE (year, month) between (2013, 12) and (2014,2);

                                                              QUERY PLAN                                                              
--------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on foo  (cost=5287.16..15670.20 rows=248852 width=14) (actual time=0.753..2.157 rows=1226 loops=1)
   Recheck Cond: ((ROW(year, month) >= ROW(2013, 12)) AND (ROW(year, month) <= ROW(2014, 2)))
   Heap Blocks: exact=1083
   ->  Bitmap Index Scan on foo_year_month_idx  (cost=0.00..5224.95 rows=248852 width=0) (actual time=0.550..0.550 rows=1226 loops=1)
         Index Cond: ((ROW(year, month) >= ROW(2013, 12)) AND (ROW(year, month) <= ROW(2014, 2)))
 Planning time: 0.099 ms
 Execution time: 2.249 ms
(7 rows)

Jako alternatywę dla zaproponowanej przez Evana Carrolla metody, którą uważam za prawdopodobnie najlepszą opcję, w niektórych przypadkach (i nie specjalnie przy użyciu PostgreSQL) korzystałem tylko z year_monthkolumny typu INTEGER(4 bajty), obliczonej jako

 year_month = year * 100 + month

Oznacza to, że kodujesz miesiąc na dwóch najbardziej po prawej stronie cyfr dziesiętnych (cyfra 0 i cyfra 1) liczby całkowitej, a rok na cyfrach od 2 do 5 (lub więcej, jeśli to konieczne).

Jest to do pewnego stopnia alternatywa biednego człowieka do budowania własnego year_monthtypu i operatorów. Ma pewne zalety, głównie „jasność intencji” i pewne oszczędności miejsca (chyba nie w PostgreSQL), a także pewne niedogodności związane z posiadaniem dwóch osobnych kolumn.

Możesz zagwarantować, że wartości są prawidłowe, po prostu dodając

CHECK ((year_date % 100) BETWEEN 1 AND 12)   /*  % = modulus operator */

Możesz mieć WHEREklauzulę wyglądającą jak:

year_month BETWEEN 201610 and 201702 

i działa skutecznie ( year_monthoczywiście jeśli kolumna jest właściwie zindeksowana).

Możesz grupować w year_monthten sam sposób, w jaki robisz to z datą i z tą samą wydajnością (przynajmniej).

Jeśli potrzebujesz oddzielić yeari month, obliczenia są proste:

month = year_month % 100    -- % is modulus operator
year  = year_month / 100    -- / is integer division 

Co jest niewygodne : jeśli chcesz dodać 15 miesięcy year_month, musisz obliczyć (jeśli nie popełniłem błędu lub przeoczenia):

year_month + delta (months) = ...

    /* intermediate calculations */
    year = year_month/100 + delta/12    /* years we had + new years */
           + (year_month % 100 + delta%12) / 12  /* extra months make 1 more year? */
    month = ((year_month%10) + (delta%12) - 1) % 12 + 1

/* final result */
... = year * 100 + month

Jeśli nie będziesz ostrożny, może to być podatne na błędy.

Jeśli chcesz uzyskać liczbę miesięcy między dwoma miesiącami rocznymi, musisz wykonać podobne obliczenia. To (z wieloma uproszczeniami) to, co naprawdę dzieje się pod maską z arytmetyką dat, która na szczęście jest przed nami ukryta przez już zdefiniowane funkcje i operatory.

Jeśli potrzebujesz wielu z tych operacji, używanie year_monthnie jest zbyt praktyczne. Jeśli tego nie zrobisz, jest to bardzo jasny sposób na wyjaśnienie swoich zamiarów.

Alternatywnie możesz zdefiniować year_monthtyp i zdefiniować operator year_month+ interval, a także inny year_month- year_month... i ukryć obliczenia. Nigdy tak naprawdę nie wykorzystałem tak często, aby poczuć potrzebę w praktyce. A date- w daterzeczywistości ukrywa cię coś podobnego.

Jako alternatywa dla metody joanolo =) (przepraszam, byłem zajęty, ale chciałem to napisać)

BIT RADOŚĆ

Zrobimy to samo, ale z kawałkami. Jedna int4w PostgreSQL jest liczbą całkowitą ze znakiem, od -2147483648 do +2147483647

Oto przegląd naszej struktury.

               bit                
----------------------------------
 YYYYYYYYYYYYYYYYYYYYYYYYYYYYMMMM

Przechowywanie miesiąca.

• Miesiąc wymaga 12 opcji pow(2,4)to 4 bity .
• Resztę poświęcamy na rok, 32-4 = 28 bitów .

Oto nasza mapa bitowa miejsc, w których przechowywane są miesiące.

               bit                
----------------------------------
 00000000000000000000000000001111

Miesiące, 1 stycznia - 12 grudnia

               bit                
----------------------------------
 00000000000000000000000000000001
               bit                
----------------------------------
 00000000000000000000000000001100

Lat Pozostałe 28 bitów pozwala nam przechowywać nasze informacje dotyczące roku

SELECT (pow(2,28)-1)::int;
   int4    
-----------
 268435455
(1 row)

W tym momencie musimy zdecydować, w jaki sposób chcemy to zrobić. Do naszych celów moglibyśmy użyć przesunięcia statycznego, gdybyśmy tylko musieli pokryć 5000 AD, moglibyśmy wrócić do 268,430,455 BCtego, do którego w zasadzie pokrywa się całość mezozoiku i wszystko, co przydatne, by iść naprzód.

SELECT (pow(2,28)-1)::int4::bit(32) << 4;
               year               
----------------------------------
 11111111111111111111111111110000

A teraz mamy podstawy naszego typu, które wygasną za 2700 lat.

Przejdźmy więc do tworzenia niektórych funkcji.

CREATE DOMAIN year_month AS int4;

CREATE OR REPLACE FUNCTION to_year_month (cstring text)
RETURNS year_month
AS $$
  SELECT (
    ( ((date[1]::int4 - 5000) * -1)::bit(32) << 4 )
    | date[2]::int4::bit(32)
  )::year_month
  FROM regexp_split_to_array(cstring,'-(?=\d{1,2}$)')
    AS t(date)
$$
LANGUAGE sql
IMMUTABLE;

CREATE OR REPLACE FUNCTION year_month_to_text (ym year_month)
RETURNS text
AS $$
  SELECT ((ym::bit(32) >>4)::int4 * -1 + 5000)::text ||
  '-' ||
  (ym::bit(32) <<28 >>28)::int4::text
$$ LANGUAGE sql
IMMUTABLE;

Szybki test pokazuje, że to działa ..

SELECT year_month_to_text( to_year_month('2014-12') );
SELECT year_month_to_text( to_year_month('-5000-10') );
SELECT year_month_to_text( to_year_month('-8000-10') );
SELECT year_month_to_text( to_year_month('-84398-10') );

Teraz mamy funkcje, których możemy używać na naszych typach binarnych.

Moglibyśmy odciąć jeszcze jeden bit od podpisanej części, zapisać rok jako dodatni, a następnie posortować go naturalnie jako podpisaną liczbę całkowitą. Gdyby prędkość miała wyższy priorytet niż miejsce do przechowywania, byłaby to droga, którą zejdziemy. Ale na razie mamy datę, która działa z mezozoikiem.

Mogę później to zaktualizować, dla zabawy.