Сразу хочу уточнить, что в базах данных я не силен, но сейчас разбираться надо самому и никого мне на замену не будет.
Ситуация. Есть база данных — MS SQL Server 14.0.2027 содержащая пять основных таблиц. В таблицах от 5 до 111 полей. Количество записей от 15 000 до 30 000 000.
Данные в таблицах нужно обрабатывать и выводить результат. Грубо говоря некий запрос.
Запрос может содержать критерии (фильтры) количеством от нуля до нескольких десятков (предварительная оценка до 80, но может и более). Критерии могут быть как простые, типа date = currentYear. Так и сложные, типа — рассчитать превышение значения поля над средним значением этого поля за указанный год (N-ое количество записей) и выбрать записи у которых превышение более чем K процентов.
Повторюсь — результат работы — один запрос учитывающий десятки различных критериев, причем какие именно будут выбраны и с какими значениями на момент разработки неизвестно (выбирает оператор в ходе работы приложения).
Возможно будет несколько понятнее если я приведу упрощенный пример единичного критерия.
Список финансовых отчетов о доходах по квартально. Выбрать компании у которых прибыль к концу года 2017 года (год выбрал оператор) превысила средне годовую этой компании (по расчетам среднего арифметического квартальных отчетов) на 5 (выбрал оператор) и более процентов.
Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать, и быстродействие под большим вопросом. Хотя может ошибаюсь. При этом уточню, что по базам данных я где-то на уровне select * from ... left join ... да create table ... Правильно расставить индексы для быстродействия — не умею. Писать сложные запросы — в лучшем случае методом тыка.
Да, допустимо чтобы расчет делался какое-то время и сохранялся. Зато потом оператор приложения мог быстро получить доступ к результатам перебирая критерии и их значения.
Как все это делать?
Здравствуйте, CyberRussia, Вы писали:
CR>Добрый день,
CR>Сразу хочу уточнить, что в базах данных я не силен, но сейчас разбираться надо самому и никого мне на замену не будет.
CR>Ситуация. Есть база данных — MS SQL Server 14.0.2027 содержащая пять основных таблиц. В таблицах от 5 до 111 полей. Количество записей от 15 000 до 30 000 000. CR>Данные в таблицах нужно обрабатывать и выводить результат. Грубо говоря некий запрос. CR>Запрос может содержать критерии (фильтры) количеством от нуля до нескольких десятков (предварительная оценка до 80, но может и более). Критерии могут быть как простые, типа date = currentYear. Так и сложные, типа — рассчитать превышение значения поля над средним значением этого поля за указанный год (N-ое количество записей) и выбрать записи у которых превышение более чем K процентов. CR>Повторюсь — результат работы — один запрос учитывающий десятки различных критериев, причем какие именно будут выбраны и с какими значениями на момент разработки неизвестно (выбирает оператор в ходе работы приложения).
CR>Возможно будет несколько понятнее если я приведу упрощенный пример единичного критерия. CR>Список финансовых отчетов о доходах по квартально. Выбрать компании у которых прибыль к концу года 2017 года (год выбрал оператор) превысила средне годовую этой компании (по расчетам среднего арифметического квартальных отчетов) на 5 (выбрал оператор) и более процентов.
CR>Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать, и быстродействие под большим вопросом. Хотя может ошибаюсь. При этом уточню, что по базам данных я где-то на уровне select * from ... left join ... да create table ... Правильно расставить индексы для быстродействия — не умею. Писать сложные запросы — в лучшем случае методом тыка. CR>Да, допустимо чтобы расчет делался какое-то время и сохранялся. Зато потом оператор приложения мог быстро получить доступ к результатам перебирая критерии и их значения. CR>Как все это делать?
Я как-то делал похожее, но там все данные легко умещались в памяти обычного компа(2.5 гига), а обновлялись через импорт csv-файла. Работало быстро, может подобный вариант подойдет?
CR>Повторюсь — результат работы — один запрос учитывающий десятки различных критериев, причем какие именно будут выбраны и с какими значениями на момент разработки неизвестно (выбирает оператор в ходе работы приложения).
CR>Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать
Если будет выбирать оператор, почему зашкаливать? У тебя вот хватит терпения сто критериев мышкой клавой выбрать?
Жизнь не обязана доставлять удовольствие. Достаточно отсутствия страданий.
Здравствуйте, Qulac, Вы писали: Q>Я как-то делал похожее, но там все данные легко умещались в памяти обычного компа(2.5 гига), а обновлялись через импорт csv-файла. Работало быстро, может подобный вариант подойдет?
Сейчас файл базы данных весит почти 6Гб. И скорее всего в ходе практической эксплуатации объем данных увеличится раза в два.
Здравствуйте, s_aa, Вы писали: _>Если будет выбирать оператор, почему зашкаливать? У тебя вот хватит терпения сто критериев мышкой клавой выбрать?
Строго говоря — выбор оператора это только ближайшая реализация. Следующая — автоматический перебор значений критериев с выбором лучшего результата.
Здравствуйте, CyberRussia, Вы писали:
CR>Как все это делать?
Многое от характера данных зависит. Если они не меняются, а только накапливаются, можешь про лямбда-архитектуру почитать или про olap.
Ещё вариант, "для бедных" — просто кешировать результаты запросов либо наиболее общих и частых подзапросов (напр.ehCache), история кэш-хитов позволит частотную статистику собрать.
Индексы тогда точно не помешают, чтобы вслепую их не ставить, можно их включать-выключать, запуская разные варианты через actual execution plan.
Здравствуйте, gyraboo, Вы писали: G>Многое от характера данных зависит. Если они не меняются, а только накапливаются, можешь про лямбда-архитектуру почитать или про olap.скор
Скорее накапливаются. Попробую почитать, спасибо.
G>Ещё вариант, "для бедных" — просто кешировать результаты запросов либо наиболее общих и частых подзапросов (напр.ehCache), история кэш-хитов позволит частотную статистику собрать.
Про кэш думал. Но сейчас бы понять, как сам запрос правильно составить, учитывая его объем (количество критериев).
Попробуйте сперва начать с linq (linq2db или entity framework). Это будет самый компактный код.
linq2db может сам сгенерировать классы c# для БД.
Для ef вроде надо использовать сторонние утилиты.
linq запросы можно протестировать в linqpad. ef в него интегрирован, linq2db надо подключать по инструкции.
SQL Server по результатам работы может сам подсказать,
где недостает каких индексов (правда тут тоже надо головой думать).
Здравствуйте, B7_Ruslan, Вы писали:
B_R>Здравствуйте, CyberRussia, Вы писали:
B_R>Попробуйте сперва начать с linq (linq2db или entity framework). Это будет самый компактный код.
EF Core я уже посмотрел и расстроился. linq2db еще не пробовал. Компактный это хорошо, но будет ли он быстродействующим?
Здравствуйте, paucity, Вы писали: P>Не очень понял вопрос... Возможно, в сторону OLAP посмотреть стоит?
Вопрос — как создаются запросы динамически в себя включающие десятки критериев (от нуля до нескольких десятков), при этом отдельный критерий это не просто выборка значения поля, а расчетное значение учитывающее другие строки (записи) в БД.
Здравствуйте, CyberRussia, Вы писали:
CR>EF Core я уже посмотрел и расстроился. linq2db еще не пробовал. Компактный это хорошо, но будет ли он быстродействующим?
К сожалению тут как повезет. Если join-ов будет больше 10 — SQL Server начнет делать не самые хорошие планы выполнения.
Еще есть ситуация,
когда часть join-ов работает для фильтрации данных (например "найти компанию"), т.е. сокращает выводимые строки,
а другая часть — на загрузку результата (например "доли владения в найденной компании"), размножает найденные строки.
Если эти типы join поместить в один запрос — ничего хорошего не будет.
Здравствуйте, B7_Ruslan, Вы писали:
B_R>Здравствуйте, CyberRussia, Вы писали:
CR>>EF Core я уже посмотрел и расстроился. linq2db еще не пробовал. Компактный это хорошо, но будет ли он быстродействующим?
B_R>К сожалению тут как повезет. Если join-ов будет больше 10 — SQL Server начнет делать не самые хорошие планы выполнения. B_R>Еще есть ситуация, B_R>когда часть join-ов работает для фильтрации данных (например "найти компанию"), т.е. сокращает выводимые строки, B_R>а другая часть — на загрузку результата (например "доли владения в найденной компании"), размножает найденные строки.
B_R>Если эти типы join поместить в один запрос — ничего хорошего не будет.
Довольно много критериев типа — посчитать средний доход компании за год (на основе квартальных отчетов) и сравнить с последним квартальным отчетом. Выбрать компании у которых превышение больше на N процентов. То есть один единственный критерий подразумевает для расчетов загрузку нескольких строк (рассчитать среднее) и по результатам только делать вывод о включении / не включении текущей строки в выборку. И подобных критериев десятки рассчитывающих разные значения.
Поэтому я боюсь, что скорость работы будет, мягко говоря, очень низкой, если решать задачу "в лоб".
Здравствуйте, CyberRussia, Вы писали:
CR>Добрый день,
CR>Сразу хочу уточнить, что в базах данных я не силен, но сейчас разбираться надо самому и никого мне на замену не будет.
CR>Ситуация. Есть база данных — MS SQL Server 14.0.2027 содержащая пять основных таблиц. В таблицах от 5 до 111 полей. Количество записей от 15 000 до 30 000 000. CR>Данные в таблицах нужно обрабатывать и выводить результат. Грубо говоря некий запрос.
Судя по объему данных и по тому, что нужен один запрос, результат будет использоваться в аналитике — отчёты, загрузка в аналитические приложения и пр.? Если да, то лучше изначально планировать перенос этих данных в хранилище с агрегацией, чтобы не делать сотни джойнов каждый раз.
CR>Мне нужно понять как такие задачи вообще решаются. Я даже не знаю как подступиться. Когда критериев было мало, я писал динамически формируемый sql в зависимости от этих критериев. Но тут, как мне кажется, и его длинна будет зашкаливать, и быстродействие под большим вопросом. Хотя может ошибаюсь. При этом уточню, что по базам данных я где-то на уровне select * from ... left join ... да create table ... Правильно расставить индексы для быстродействия — не умею. Писать сложные запросы — в лучшем случае методом тыка. CR>Да, допустимо чтобы расчет делался какое-то время и сохранялся. Зато потом оператор приложения мог быстро получить доступ к результатам перебирая критерии и их значения. CR>Как все это делать?
Можно написать хранимую процедуру с параметризацией запроса. Во-первых, план исполнения будет кэширован, так же как и наиболее часто возвращаемые данные. Во-вторых, можно попробовать создать условия выборки такого плана (это не всегда работает, надо смотреть конкретный случай):
Select ...
From A
Left join B On...
...
Where (A.col_1 = @param_1 or @param_1 IS NULL)
And ...
Если параметр передан — критерий применится, а если пришел пустой — выберутся все значения по данной колонке, как будто параметра нет.
Усложнение возникает, когда есть несколько значений, то это решается через превращение строки в табличку через табличную функцию.
Здравствуйте, Milena, Вы писали: M>Судя по объему данных и по тому, что нужен один запрос, результат будет использоваться в аналитике — отчёты, загрузка в аналитические приложения и пр.? Если да, то лучше изначально планировать перенос этих данных в хранилище с агрегацией, чтобы не делать сотни джойнов каждый раз.
Заказчик хочет сделать аналитику как раз на основе таких запросов. Заказчик в технологиях вообще не разбирается. Да и для меня область незнакомая, не сталкивался раньше.
CR>Довольно много критериев типа — посчитать средний доход компании за год (на основе квартальных отчетов) и сравнить с последним квартальным отчетом. Выбрать компании у которых превышение больше на N процентов. То есть один единственный критерий подразумевает для расчетов загрузку нескольких строк (рассчитать среднее) и по результатам только делать вывод о включении / не включении текущей строки в выборку. И подобных критериев десятки рассчитывающих разные значения. CR>Поэтому я боюсь, что скорость работы будет, мягко говоря, очень низкой, если решать задачу "в лоб".
Тут я хорошо посоветовать не могу, может другие что-нибудь напишут.
Я бы разделил БД. Одна для ввода данных. А другая для отчетов.
На другом сервере восстанавливаете БД из резервной копии.
Пишите программу, которая создает в текущей схеме денормализованные данные для ускорения расчетов:
1) создает новые колонки и таблицы с агрегированными данными, индексы. Обновление схемы можно выполнить с помощью FluentMigrator.
2) выполняет возможные предварительные расчеты для предикатов.
Целевая программа будет крутиться в БД для отчетов.
Если все агрегированные значения посчитать заранее — может быть будет работать достаточно быстро.
Здравствуйте, CyberRussia, Вы писали:
CR>Как все это делать?
Если в запросах будут использоваться много критериев и они часто будут разные, то индексов на всех не напасешься и часто будет идти полное (или частичное) сканирование таблиц. Для SQL-баз поможет только увеличение RAM. Можно также рассчитывать некоторые агрегации заранее и сохранять в БД для последующих запросов.
Если скорости недостаточно, то возможно подойдут аналитические колоночные БД, например clickhouse.
Из плюсов — быстрая работа и поддержка SQL для аналитиков.
Из минусов:
— придется строить процессы периодической выгрузки данных из MSSQL в другую БД
— возможно реализовывать другую схему данных, более подходящую для запросов
Здравствуйте, CyberRussia, Вы писали:
CR>Вопрос — как создаются запросы динамически в себя включающие десятки критериев (от нуля до нескольких десятков), при этом отдельный критерий это не просто выборка значения поля, а расчетное значение учитывающее другие строки (записи) в БД.
Т.е. ты хочешь написать генератор произвольных запросов, где критерии и фильтры будут задаваться чайником кликающим мышкой без знания sql?
CR>Может и стоит, если бы я знал, что это такое.
OLAP — Online analytical processing — если в двух словах, еще одна специальная "база," куда ты загрузишь данные из основной БД, а она пред-расчитает тебе различные агрегаты по основным и расчетным полям.
Пользователь может "крутить", slice'ить и dice'ить, сворачивать и разворачивать эти данные по различным измерения (дата, компания, менеджер, и т.п.) или через спец интерфейс или в том же Excel'е.
Здравствуйте, B7_Ruslan, Вы писали: B_R>К сожалению тут как повезет. Если join-ов будет больше 10 — SQL Server начнет делать не самые хорошие планы выполнения.
Вот с чего это вдруг?
Всё, что нас не убивает, ещё горько об этом пожалеет.
Здравствуйте, CyberRussia, Вы писали: CR>Как все это делать?
Ну, даже не знаю. Я могу подсказать как это не делать...
Смотри. В Microsoft Office входит (или когда-то входил?) такой пакет как Microsoft Access. Его, насколько я помню, можно было даже натравить на SQL Server... Но не суть, главное в нем есть то, что тебе нужно написать — GUI для создания запросов. Поставь, поиграйся. Это тебе даст понимание в какую жопу ты попал. И да, еще раз на всякий случай — так делать не нужно. Но хоть будет от чего оттолкнуться.
Всё, что нас не убивает, ещё горько об этом пожалеет.
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, Qulac, Вы писали: Q>>Я как-то делал похожее, но там все данные легко умещались в памяти обычного компа(2.5 гига), а обновлялись через импорт csv-файла. Работало быстро, может подобный вариант подойдет? CR>Сейчас файл базы данных весит почти 6Гб. И скорее всего в ходе практической эксплуатации объем данных увеличится раза в два.
Ну для серверов это не много и плюс легко протестировать.
Здравствуйте, paucity, Вы писали: P>Т.е. ты хочешь написать генератор произвольных запросов, где критерии и фильтры будут задаваться чайником кликающим мышкой без знания sql?
Не совсем произвольные, а предопределенные GUI
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, paucity, Вы писали: P>>Т.е. ты хочешь написать генератор произвольных запросов, где критерии и фильтры будут задаваться чайником кликающим мышкой без знания sql? CR>Не совсем произвольные, а предопределенные GUI
Здравствуйте, Ромашка, Вы писали: Р>Смотри. В Microsoft Office входит (или когда-то входил?) такой пакет как Microsoft Access. Его, насколько я помню, можно было даже натравить на SQL Server... Но не суть, главное в нем есть то, что тебе нужно написать — GUI для создания запросов. Поставь, поиграйся. Это тебе даст понимание в какую жопу ты попал. И да, еще раз на всякий случай — так делать не нужно. Но хоть будет от чего оттолкнуться.
Честно говоря — не понял. При чем тут Access? Те запросы, которые они генерирует через визуальный редактор мне ни горячо и ни холодно. Во-первых, по условиям критериев, все равно без знания sql ничего не напишешь. Во-вторых, проблема в количестве этих критериев и соответственно сложности результирующего запроса, если его делать как одну команду sql.
Здравствуйте, Serginio1, Вы писали: S> Можно вообще кодогенерацию прикрутить. https://infostart.ru/1c/articles/547389/
Можно. Но проблема, как мне видится, не в том, чтобы создать один sql запрос, а в его потенциальной сложности и длительности выполнения. В целом, как я понял из обсуждения моего вопроса, нужно копать в сторону OLAP
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, Serginio1, Вы писали: S>> Можно вообще кодогенерацию прикрутить. https://infostart.ru/1c/articles/547389/ CR>Можно. Но проблема, как мне видится, не в том, чтобы создать один sql запрос, а в его потенциальной сложности и длительности выполнения. В целом, как я понял из обсуждения моего вопроса, нужно копать в сторону OLAP
ОЛАП хороши, когда ты точно знаешь чего хочешь. К сожалению в большинстве случаев пользователю нужно больше.
Поэтому существуют https://www.linqpad.net/
и солнце б утром не вставало, когда бы не было меня
Здравствуйте, Serginio1, Вы писали: S> ОЛАП хороши, когда ты точно знаешь чего хочешь. К сожалению в большинстве случаев пользователю нужно больше. S>Поэтому существуют https://www.linqpad.net/
А оно "радостно" не навернется, когда будет штук 80 критериев, каждый из который для своего расчета будет еще несколько записей из БД брать?
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, Serginio1, Вы писали: S>> ОЛАП хороши, когда ты точно знаешь чего хочешь. К сожалению в большинстве случаев пользователю нужно больше. S>>Поэтому существуют https://www.linqpad.net/ CR>А оно "радостно" не навернется, когда будет штук 80 критериев, каждый из который для своего расчета будет еще несколько записей из БД брать?
Не навернется, но будет дольше выполняться. Это не зависит от LinqPad.
А от того как составишь запрос. Кроме того SQL сам может создать хэш талицы и прочее для небольших таблиц.
Это уже от Sql зависит.
Ну а 6ГБ это ни очем. Пробежаться по всем записям не проблема, особенно когда они все в памяти. У тебя сколько памяти на сервере?
и солнце б утром не вставало, когда бы не было меня
Здравствуйте, Serginio1, Вы писали:
S>>> Можно вообще кодогенерацию прикрутить. https://infostart.ru/1c/articles/547389/ CR>>Можно. Но проблема, как мне видится, не в том, чтобы создать один sql запрос, а в его потенциальной сложности и длительности выполнения. В целом, как я понял из обсуждения моего вопроса, нужно копать в сторону OLAP S> ОЛАП хороши, когда ты точно знаешь чего хочешь. К сожалению в большинстве случаев пользователю нужно больше. S>Поэтому существуют https://www.linqpad.net/
Что это за зверь? Скриншоты интересные, но из описания на сайте не очень понятно что он вообще делает?
Здравствуйте, gyraboo, Вы писали:
G>Здравствуйте, Serginio1, Вы писали:
S>>>> Можно вообще кодогенерацию прикрутить. https://infostart.ru/1c/articles/547389/ CR>>>Можно. Но проблема, как мне видится, не в том, чтобы создать один sql запрос, а в его потенциальной сложности и длительности выполнения. В целом, как я понял из обсуждения моего вопроса, нужно копать в сторону OLAP S>> ОЛАП хороши, когда ты точно знаешь чего хочешь. К сожалению в большинстве случаев пользователю нужно больше. S>>Поэтому существуют https://www.linqpad.net/
G>Что это за зверь? Скриншоты интересные, но из описания на сайте не очень понятно что он вообще делает?
Универсальный отчет
Well, you don’t have to! With LINQPad, you can interactively query databases in a modern query language: LINQ. LINQPad includes an optimized engine to build typed data contexts on the fly, and includes drivers and formatters for:
SQL Server, SQL Azure, SQL CE, Oracle, SQLite, PostgreSQL and MySQL
Azure Table Storage & Azure Metadata
Entity Framework Core custom contexts
Microsoft Reactive Extensions and LINQ to Logs & Traces
OData and SharePoint
Third-party ORMs including Mindscape LightSpeed, DevArt’s LinqConnect, LLBLGen, DevExpress eXpress Persistent Objects and DevForce
LINQPad also supports all the LINQ APIs in the .NET Framework, including LINQ to XML, PLINQ, Entity Framework and LINQ to SQL.
Я так понимаю, что строит классы по Base First и выполняет запросы.
Сам не пользовался
В появившемся окне указать путь к сборке с объектами данных, а также к файлу конфигураци приложения (из него используется строка соединения с базой данных). После нажатия кнопки OK новое подключение появится в главном окне программы в списке подключений.
Здравствуйте, Serginio1, Вы писали: S> Ну а 6ГБ это ни очем. Пробежаться по всем записям не проблема, особенно когда они все в памяти. У тебя сколько памяти на сервере?
Сервера у заказчика еще нет. Он только в планах приобретения. Пока не полная база стоит на моей рабочей машине. 6Гб это то, что у меня сейчас. Зная заказчика, я не думаю, что он будет дорогое железо покупать.
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, Serginio1, Вы писали: S>> Ну а 6ГБ это ни очем. Пробежаться по всем записям не проблема, особенно когда они все в памяти. У тебя сколько памяти на сервере? CR>Сервера у заказчика еще нет. Он только в планах приобретения. Пока не полная база стоит на моей рабочей машине. 6Гб это то, что у меня сейчас. Зная заказчика, я не думаю, что он будет дорогое железо покупать.
Ну память недорогая вещь, а там большую роль играет именно память. Конечно и кэши процессора вещь тоже нужная.
и солнце б утром не вставало, когда бы не было меня
В таком случае обычно делается набор отчетов и постепенно дополняется по мере необходимости.
Ну или попробуйте сделать один универсальный, но не думаю что сможете вместить в него все. И главное, убедиться, что он не врет нигде.
Здравствуйте, CyberRussia, Вы писали:
CR>Можно. Но проблема, как мне видится, не в том, чтобы создать один sql запрос, а в его потенциальной сложности и длительности выполнения. В целом, как я понял из обсуждения моего вопроса, нужно копать в сторону OLAP
Сначала лучше посмотреть на индексы, индексированные вьюхи, планы запросов и частичную денормализацию данных, прежде чем связываться с OLAP
"For every complex problem, there is a solution that is simple, neat,
and wrong."
Здравствуйте, CyberRussia, Вы писали:
CR>Сервера у заказчика еще нет. Он только в планах приобретения. Пока не полная база стоит на моей рабочей машине. 6Гб это то, что у меня сейчас. Зная заказчика, я не думаю, что он будет дорогое железо покупать.
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, paucity, Вы писали: P>>Не очень понял вопрос... Возможно, в сторону OLAP посмотреть стоит? CR>Вопрос — как создаются запросы динамически в себя включающие десятки критериев (от нуля до нескольких десятков), при этом отдельный критерий это не просто выборка значения поля, а расчетное значение учитывающее другие строки (записи) в БД.
Ну, так и создаются. Пишем запрос, который рассчитывает значения для "других строк".
например, запрос, который рассчитывает среднюю оценку студента:
select studentID, avg(rate) from studentRates group by studentID
Найдём для каждого студента курсы, которые он сдал с оценкой не ниже его среднего балла:
select courseId from studentRates inner join
(select studentID, avg(rate) from studentRates group by studentID) avgRates on avgRates.studentId = studentRates.studentId
where studentRates.rate > avgRates.rate
Ну, или сразу подзапросом:
select courseId from studentRates
where studentRates.rate > (select avg(rate) from studentRates avgRates where avgRates.studentId = studentRates.studentId)
Если вопрос в том, как строить такие запросы "мышкой" — то это не в Базы Данных, а в UI спрашивать надо.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Легко, организуешь подсчет агрегатов в OLAP или Indexed View (aka Materialized View in PostgreSQL или Oracle): выручка компании по месяцам, продажи по категориям товаров по месяцам и т.д.
В UI создаешь мастер для построения фильтров и вуаля. Будет работать быстро.
Ни какой LinqToSql, как тебе тут советовали, тебе не поможет при росте бд (потому что сейчас, когда по 30К записей в бд, то всю бд можно выгрузить в память и работать с ней), но вот когда записей станет сотни миллионов, вт тогда уже в память не выгрузишь). Хранимки тоже будут тормозить.
Здравствуйте, Sinclair, Вы писали: S>Если вопрос в том, как строить такие запросы "мышкой" — то это не в Базы Данных, а в UI спрашивать надо.
Нет, вопрос изначально в том, что делать когда таких критериев десятки. Не приведет ли это к существенно длительному времени выполнения запроса, а соответственно, не требуется ли решать такую задачу как-то иначе, чем записывание всех критериев в один sql запрос.
Здравствуйте, CyberRussia, Вы писали:
CR>Нет, вопрос изначально в том, что делать когда таких критериев десятки. Не приведет ли это к существенно длительному времени выполнения запроса
Почти наверняка приведет.
CR>, а соответственно, не требуется ли решать такую задачу как-то иначе, чем записывание всех критериев в один sql запрос.
Скорее всего, потребуется. А как именно — я не знаю, я не до конца понимаю твой сценарий. Вот так навскидку, голый SQL тебе не очень-то и подходит.
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, Sinclair, Вы писали: S>>Если вопрос в том, как строить такие запросы "мышкой" — то это не в Базы Данных, а в UI спрашивать надо. CR>Нет, вопрос изначально в том, что делать когда таких критериев десятки. Не приведет ли это к существенно длительному времени выполнения запроса, а соответственно, не требуется ли решать такую задачу как-то иначе, чем записывание всех критериев в один sql запрос.
Количество критериев мало влияет на длительность. Там есть два фактора:
1. Объём сканируемых данных, необходимых для выполнения запроса.
2. Качество плана исполнения — то есть разница между "необходимым" объёмом работы, и фактически потраченным.
Количество параметров может повлиять на второе. А первое определяется задачей, и наличием индексов.
Скорее всего, у вас будет вовсе не произвольная схема; реальным пользователям не нужна тьюринг-полнота. Значит, можно доработать ваши пять исходных таблиц несколькими view для агрегатов, и дать оператору выбирать из них.
По мере набора статистики запросов, можно докидывать индексы в таблицы, а популярные view сделать материализованными.
Уйдемте отсюда, Румата! У вас слишком богатые погреба.
Здравствуйте, CyberRussia, Вы писали: CR>Нет, вопрос изначально в том, что делать когда таких критериев десятки. Не приведет ли это к существенно длительному времени выполнения запроса, а соответственно, не требуется ли решать такую задачу как-то иначе, чем записывание всех критериев в один sql запрос.
Я вообще нифига не понимаю, что вы обсуждаете...
Если быть точным — количество критериев на время выполнения запроса не влияет. Влияет их качество. Чем меньшее количество записей отфильтровывает критерий, тем быстрее запрос. То есть в любом случае запрос с критериями будет не медленнее, чем запрос без них. Агрегатные функции (aka среднее значение) отдельно, это не критерий, критерий тут "не превышает N процентов".
Напихаешь много агрегатов — будет тормозить, запихаешь туда еще двести "простых" (как ты их называешь) критериев — не будет.
Нет, задача не решается как-то иначе — любые другие решения хуже. Дешевле один раз пройтись по таблице и посчитать все агрегаты, чем делать это несколько раз.
Всё, что нас не убивает, ещё горько об этом пожалеет.
Здравствуйте, Ромашка, Вы писали:
Р>Если быть точным — количество критериев на время выполнения запроса не влияет.
Насколько я понимаю, его "критерии" могут вызывать доп. джойны и доп. условия джойнов, что определенно повлияет на время запроса.
И чем больше будет таких "критериев", тем сложнее может оказаться жизнь оптимизатора и тем меньше шансов на то, что он справится со своей задачей оптимальным образом.
Подводных камней там хватает, сам сталкивался с похожей задачей в своё время.
Р>Агрегатные функции (aka среднее значение) отдельно, это не критерий, критерий тут "не превышает N процентов".
А с точки зрения функционала это как-то облегчает/меняет задачу?
Агрегаты считать всё равно либо надо, либо не надо, в зависимости от наличия/отсутствия "критерия", если я правильно понимаю его задачу.
Здравствуйте, _ABC_, Вы писали: Р>>Если быть точным — количество критериев на время выполнения запроса не влияет. _AB>Насколько я понимаю, его "критерии" могут вызывать доп. джойны и доп. условия джойнов, что определенно повлияет на время запроса.
Еще раз, количество критериев на время запроса не влияет. Величина выборки, в том числе join — да.
Но у автора не mysql, где за сорок лет не удосужились реализовать совсем ничего кроме nested loop, поэтому join-ы это не столь существенно.
_AB>И чем больше будет таких "критериев", тем сложнее может оказаться жизнь оптимизатора и тем меньше шансов на то, что он справится со своей задачей оптимальным образом.
Наоборот. Чем больше критериев тем проще оптимизатору и тем больше шанс на выбор оптимального плана.
_AB>Подводных камней там хватает, сам сталкивался с похожей задачей в своё время.
Да везде полно подводных камней. Он для того и программист, чтобы с ними справляться.
Вопрос был в другом — нужно ли решать задачу другим путем. Ответ — все альтернативы написанию запроса с 200 "критериями" хуже.
Р>>Агрегатные функции (aka среднее значение) отдельно, это не критерий, критерий тут "не превышает N процентов". _AB>А с точки зрения функционала это как-то облегчает/меняет задачу? _AB>Агрегаты считать всё равно либо надо, либо не надо, в зависимости от наличия/отсутствия "критерия", если я правильно понимаю его задачу.
Да. Агрегаты считаются один раз. То есть это опять же аргумент в пользу "запроса с 200 'критериями'".
Всё, что нас не убивает, ещё горько об этом пожалеет.
Здравствуйте, CyberRussia, Вы писали:
CR>Как все это делать?
Не надо изобретать велосипед.
Попробуй предложить заказчику использовать готовое решение для аналитики и отчетов.
Если у вас Microsoft, то это Micrsooft SQL Server Reporting Services например (на базе OLAP), который "из коробки", ну или PowerBI.
Там вся агрегация уже сделана, ничего тормозить не будет.
Но вообще BI (Business Intelligence, отчеты, графики, вот это вот все) — это отдельная такая себе область.
В общем для создания отчетов, код писать скорее всего не надо вообще.
Здравствуйте, bnk, Вы писали: bnk>Попробуй предложить заказчику использовать готовое решение для аналитики и отчетов. bnk>Если у вас Microsoft, то это Micrsooft SQL Server Reporting Services например (на базе OLAP), который "из коробки", ну или PowerBI.
Они предоставляют возможность "из коробки" сбора данных, их анализа и отображения в GUI ? Причем все так, как хочет заказчик, который сам с трудом формулирует, что он хочет, а значит ничего настроить лично не сумеет.
Здравствуйте, _ABC_, Вы писали: _AB>Насколько я понимаю, его "критерии" могут вызывать доп. джойны и доп. условия джойнов, что определенно повлияет на время запроса. _AB>И чем больше будет таких "критериев", тем сложнее может оказаться жизнь оптимизатора и тем меньше шансов на то, что он справится со своей задачей оптимальным образом.
Именно так.
Здравствуйте, CyberRussia, Вы писали:
CR>Здравствуйте, bnk, Вы писали: bnk>>Попробуй предложить заказчику использовать готовое решение для аналитики и отчетов. bnk>>Если у вас Microsoft, то это Micrsooft SQL Server Reporting Services например (на базе OLAP), который "из коробки", ну или PowerBI.
CR>Они предоставляют возможность "из коробки" сбора данных, их анализа и отображения в GUI ? Причем все так, как хочет заказчик, который сам с трудом формулирует, что он хочет, а значит ничего настроить лично не сумеет.
Ну да. Причем мышкой, ничего писать не надо.
Если заказчик не знает, чего хочет, это нормально. Большинство не знает.
Специалисты для того и существуют, чтобы сказать заказчику, чего он хочет. Даже специализация есть — BA (бизнес аналитик)
Просто предоставь заказчику готовые варианты, пусть выбирает.
Здравствуйте, bnk, Вы писали: bnk>Просто предоставь заказчику готовые варианты, пусть выбирает.
Вариант, конечно. Но ему тогда мне не за что будет платить
Здравствуйте, CyberRussia, Вы писали:
CR>Вариант, конечно. Но ему тогда мне не за что будет платить
Это лучше, чем попытка в одиночку создать что-то похожее. Может и создашь, но не быстро и с очень высокой степенью вероятности успеешь до этого испортить репутацию у заказчика.
Здравствуйте, _ABC_, Вы писали: _AB>Это лучше, чем попытка в одиночку создать что-то похожее. Может и создашь, но не быстро и с очень высокой степенью вероятности успеешь до этого испортить репутацию у заказчика.
Меня не очень пугает испортить репутацию у того, кто не может даже внятно документацию бизнес-логики написать и при попытках уточнить в диалоге говорит: "я полагаюсь на вашу компетенцию". А при предложении продемонстрировать промежуточный релиз: "Зачем, я уверен, что вы все правильно поняли и правильно делаете".
