sql samples

sql samples.sql

# Тестовый пример на подсчет аггрегаций
SELECT g.*
FROM goods AS g
  JOIN (SELECT tg.goods_id
        FROM tags_goods AS tg
        GROUP BY tg.goods_id
        HAVING COUNT(tg.tag_id) = (SELECT COUNT(*) AS t_count
                                   FROM tags AS t)) AS res ON res.goods_id = g.id;

# Вставка из генератора
INSERT INTO stat_context_matches (contexts) 
SELECT '{"user-location": [250000100000]}'::jsonb
FROM generate_series(0, 100); 

# Размер таблицы
SELECT pg_size_pretty( pg_total_relation_size('stats') );

# Запрос на выборку данных по отчету для объявления с использованием индекса и nested loop
EXPLAIN ANALYZE SELECT s.*, m.* FROM public.stats AS s 
JOIN public.stat_context_matches AS m 
ON (s.match_id = m.id AND s.partition_key = m.partition_key) 
WHERE s.time > date('2020-08-27') AND s.advertisement_id = 2822 AND s.partition_key = '84'