Различия

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

--- it:postgresql:sql [2022/11/25 10:55] – ura2404
+++ it:postgresql:sql [2023/04/16 11:20] (текущий) – ura2404
@@ Строка 10: / Строка 10: @@
 end
 </code>
+----
+==== Заполнить таблицу миллионом строк ====
+<code sql>
+CREATE TABLE foo (c1 integer, c2 text);
+INSERT INTO foo
+  SELECT i, md5(random()::text)
+  FROM generate_series(1, 1000000) AS i;
+</code>
+----
+==== Удалить дубликаты ====
+<code sql>
+DELETE FROM wgdump.dump WHERE ctid NOT IN (
+	SELECT max(ctid) FROM wgdump.dump GROUP BY ts
+);
+</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);