Различия

Показаны различия между двумя версиями страницы.

--- it:postgresql:sql [2023/03/17 18:33] – ura2404
+++ it:postgresql:sql [2023/04/16 11:20] (текущий) – ura2404
@@ Строка 29: / Строка 29: @@
 );
 </code>
+==== Пример рандома ====
+<code sql>
+--explain ANALYZE
+WITH
+temperature AS (
+	SELECT ts + (INTERVAL '60 minutes' * random()) AS ts, city, (30*random())::int AS temperature
+	FROM generate_series('2022-01-01' :: TIMESTAMP,
+	                     '2022-01-31', '1 day') AS ts,
+	     unnest(array['Moscow', 'Berlin']) AS city
+),
+humidity AS (
+	SELECT ts + (INTERVAL '60 minutes' * random()) AS ts, city, (100*random())::int AS humidity
+	FROM generate_series('2022-01-01' :: TIMESTAMP,
+                     '2022-01-31', '1 day') AS ts,
+     	unnest(array['Moscow', 'Berlin']) AS city
+)
+SELECT t.ts, t.city, t.temperature, h.humidity
+FROM temperature AS t
+LEFT JOIN LATERAL
+  ( SELECT * FROM humidity
+    WHERE city = t.city AND ts <= t.ts
+    ORDER BY ts DESC LIMIT 1
+  ) AS h ON TRUE
+--WHERE t.ts < '2022-01-05'
+</code>
+==== Колво дней в месяце ====
+SELECT
+  ts.day::date AS ts
+ ,extract(day from date_trunc('month',ts.day) + interval '1 month -1 day')
+FROM
+ generate_series('2023-01-01', '2023-12-01', interval '1 month') AS ts(day);