Как объединить несколько запросов выбора из одной таблицы
Я хотел бы запросить таблицу с несколькими столбцами (например, 4 ниже), из которой я хотел бы создать таблицу моментальных снимков, которая может предоставлять информацию о количестве атрибутов каждый день.
Например, сверху
В этом случае у нас могут быть миллионы строк в исходной таблице с более чем 100 столбцами, и мы хотели бы каждый день заполнять таблицу моментальных снимков количеством пользователей с ненулевыми значениями для каждого атрибута и датой создания этого моментального снимка.
Я формулирую свой запрос следующим образом: -
select COUNT(DISTINCT userId) as userIdCount from orig_table where
attr1 IS NOT NULL UNION Select COUNT(DISTINCT userId) as userIdCount
from orig_table where attr2 IS NOT NULL UNION Select COUNT(DISTINCT
userId) as userIdCount from orig_table where attr3 IS NOT NULL;
Есть ли оптимизированный способ написать несколько запросов на выборку вместе. Я начал с создания множественного запроса на выборку и вставки его в выходную таблицу один за другим, но это кажется плохим шаблоном. Пожалуйста, предложите, как этого добиться, чтобы это можно было масштабировать.
PS: Новичок (который впервые изучает запросы к БД)
Спасибо, Абхишек! Любое предложение о том, как объединить несколько вариантов выбора для создания представлений.
Я думаю, вы имеете в виду материализованный взгляд . Обычный view пересчитывает запрос для каждого select из него, тогда как materialized view сохраняет снимок результата до тех пор, пока вы не обновите его. Часть проблемы с множественным выбором можно решить с помощью динамического SQL в блоке PL/pgSQL.
Удалены конфликтующие теги товаров. Пожалуйста, добавьте обратно тот, который вы действительно используете!
Ответы 1
Я думаю, вы имеете в виду материализованный взгляд . Обычный view пересчитывает запрос для каждого select из него, тогда как materialized view сохраняет снимок результата до тех пор, пока вы не обновите его.
Часть проблемы с множественным выбором можно решить с помощью динамического SQL в блоке PL/pgSQL.
do $f$
declare column_name_ text;
dynamic_sql_query text;
dynamic_sql_query_per_table text:=$$SELECT COUNT(DISTINCT "userId") AS %1$I
,'%2$I' as "attrName"
,now() AS "dateWhenSnapshotInserted"
FROM orig_table
WHERE %2$I IS NOT NULL$$;
begin
for column_name_ in select column_name
from information_schema.columns
where table_name='orig_table'
loop
dynamic_sql_query:=concat_ws(' UNION ALL '
,dynamic_sql_query
,format( dynamic_sql_query_per_table
,column_name_||'Count'
,column_name_));
end loop;
execute format('CREATE MATERIALIZED VIEW orig_table_counts AS %s',dynamic_sql_query);
end $f$;
select * from orig_table_counts;
- concat_ws() заботится о том, чтобы поставить
union allмежду каждым запросом, который собирает значения для каждого столбца. Он пропускаетnullаргументы, поэтому изначально объявленный, но незаполненныйdynamic_sql_queryпредотвращает использование ведущего/конечногоunionразделителя. $$— это кавычки, которые позволяют использовать внутри них другие типы кавычек.%1$Iи%2$Iявляются заполнителями format(), указывающими ему вставить 1-й/2-й аргумент в качестве необязательного идентификатораIв двойных кавычках.%sсообщает ему, чтобы он поместил еще одну строку в виде простой строки без какой-либо специальной обработки.- Обязательно обратите внимание на чувствительность вашего идентификатора к регистру: если вы не используете двойные кавычки
", все они сворачиваются в нижний регистр, а это означает, чтоuserIdна самом деле может называтьсяuserid.
попробуйте использовать представления.