En vous connectant, vous pouvez utiliser des namespaces (e.g. https://paste.iiens.net/exemple/), supprimer vos pastes, etc.

UDA PG   (raw) expire le 22 February 2021 | 15:54

  1. CREATE TYPE date_and_rn AS (
  2.     d  date,
  3.     rn int
  4. );
  5.  
  6. CREATE OR REPLACE FUNCTION reduce(previous_state date_and_rn, current_row date, max_interval interval) RETURNS date_and_rn AS
  7. $$
  8.     SELECT CASE
  9.         -- still within the max range, we simply increment rn
  10.         WHEN ((current_row - (previous_state).d) || ' days')::interval <= max_interval THEN ((previous_state).d, (previous_state).rn + 1)::date_and_rn
  11.         -- new group, starting from the current row
  12.         ELSE (current_row, 1)::date_and_rn
  13.     END;
  14. $$
  15. IMMUTABLE
  16. LANGUAGE SQL;
  17.  
  18. CREATE OR REPLACE FUNCTION finalizer(output date_and_rn) RETURNS int AS
  19. $$
  20.     SELECT (output).rn;
  21. $$
  22. IMMUTABLE
  23. STRICT
  24. LANGUAGE SQL;
  25.  
  26. CREATE AGGREGATE not_a_good_name(date, interval)
  27. (
  28.     SFUNC = reduce,
  29.     STYPE = date_and_rn,
  30.     FINALFUNC = finalizer
  31. );
  32.  
  33. WITH values(x) AS (
  34.     VALUES (1), (2), (3), (5), (6)
  35. ), as_dates(d) AS (
  36.     SELECT ('2020-01-01'::date + (x || ' months')::interval)::date
  37.     FROM values
  38. )
  39. SELECT d, not_a_good_name(d, '3 months'::interval) OVER (ORDER BY d)
  40. FROM as_dates
  41. ;
  42.  
  43. /* OUTPUT
  44.  
  45. 2020-02-01;1
  46. 2020-03-01;2
  47. 2020-04-01;3
  48. 2020-06-01;1
  49. 2020-07-01;2
  50.  
  51. */
  52.  
  53.  

Écrivez votre texte ci-dessous !

Ajoutez "+++" au début d'une ligne pour la mettre en valeur !

Langage Accès Expire dans


Options