Задача выбора случайных строчек из таблицы довольно часто возникает перед разработчиками.
В случае, если используется СУБД MySQL, обычно она решается примерно следующим способом:

SELECT *
FROM users
WHERE role_id=5
ORDER BY rand()
LIMIT 10

Такой код работает крайне медленно для больших таблиц.
Если в запросе не нужно использовать WHERE или таблица небольшая, есть эффективные решения, например habrahabr.ru/post/54176 или habrahabr.ru/post/55864.
Но готовых решений для большой таблицы и необходимости фильтровать по условию, получая при каждом запросе новые значения, я не нашел, поэтому описание моего способа под катом.

В современных информационных системах, процесс принятие решения, зачастую, строится на основании консолидированной информации. На практике же, при разработке бизнес-логики, оперирующей подобной информацией, очень часто приходится преобразовать строки в столбцы.

В синтаксисе T-SQL для выполнения подобного преобразования предусмотрена отдельная конструкция PIVOT. Стоит заметить, что в SQL Server 2000 поддержки конструкции PIVOT еще не было, поэтому аналогичные задачи решались через множественные CASE WHEN.

Собственно, почему я упомянул о CASE WHEN, если есть PIVOT? Ведь, по определению, PIVOT более элегантная конструкция и, соответственно, должна быть более эффективной.

Проверим это на практике…

Не секрет, что зашифрованные параметры (т.е. имеющие тип Encrypted), используемые в IBM DataStage в версиях до 8.7 очень легко расшифровать. Эти зашифрованные параметры часто используются для передачи паролей, необходимых для соединения с базами данных.
При постороении корпоративных ODS (а в некоторых случаях даже и в случае хранилищ данных) имеет смысл создавать универсальные джобы — так называемые генерики, которые полностью конфигурируются извне и не содержат специфичной для каждой таблицы информации, а поэтому их можно использовать для многих ETL процессов. Особенно это необходимо при извлечении данных из баз данных источников (Extraction). В таком случае необходимо хранить в файлах конфигураии пароли для каждого источника данных. И вам приходится, прогибаясь под политики безопасности различных предприятий, делать вид, что это надежный алгоритм шифрования и хранить пароли к корпоративным данным в зашифрованном DataStage виде.
Но проблемы возникают, если вы захотите передать такие параметры в джоб. Какие проблемы и как их решать я и напишу в этой статье.

Сначала результат, а потом разбор полетов. Допустим, вы создали запрос типа select [document].[id], [document].[name] from [SomeDocuments][document], но вместо ожидаемого

  id = 1
 name = d1

получаете

 document = 1
 document = d1

А теперь, если интересно будем разбираться.

Доброго дня, хабровчане! Пришлось мне в проекте столкнуться с точностью вычислений в MS SQL Server и я обнаружил не совсем интуитивное поведение при выполнении казалось бы интуитивных операций.

Для затравки вопрос (попробуйте ответить на него, не выполняя):
Каков будет результат операции?

declare @var1 decimal(38,10) = 0.0000007,
        @var2 decimal(38,10) = 1;
select @var1 * @var2;

Ответ и объяснение под катом

Здравствуй Хабр!

Совсем недавно в нашей компании проходил очередной Хакатон. И в его рамках мне захотелось убить время поинтересней сделать полезную вещь, как для себя так и для других разработчиков. Выбор пал на этакий валидатор sql кода, который бы проверял его на разные правила что не под силу компилятору и те что могут пропустить ребята которые делают Code Review. Таких правил можно придумать массу, начиная от простого “Добавлять GO в конце запроса” и заканчивая более сложными “Использовать View вместо Table”. И самое главное, этот валидатор никоим образом не должен добавлять время разработчику на его использование, т.е. попросту говоря, он должен валидировать себе где-то автоматически, вне зависимости от действий разработчика.

Так уж исторически сложилось, что весь sql-код перед тем как выйти в продакшн (т.е. исполнится на основной БД) сохраняется у нас в GIT репозитории, куда попадает напрямую от разработчиков (естественно после Code Review). Так вот, возникла идея добавить git-hook в этом репозитории который бы валидировал sql-код и если он не валидный то коммит бы возвращался разработчику на доработку. Немного тяжело представить, легче нарисовать:

Хочу задать этот вопрос Хабровчанам.

Современные информационные системы строятся на различных видах СУБД и все же реляционные СУБД остаются самыми распространенными и используемыми. Интересная статистика на эту тему ТУТ и ТУТ.


При разработке и модификации систем уровень формализации знаний аналитиков и разработчиков остается небольшим (автоматизации создания умных запросов или с учетом ряда четких правил) и чаще всего результирующие SQL запросы написаны «нормально», «как привык», «так пишут у нас на фирме», а вопросы оптимизации остаются на этап выполнения запросов в СУБД и последующие этапы оптимизации (в худшем случае ждут, когда все начинает тормозить).

Объем ручного кода остается большим даже несмотря на

В статье я описал несколько примеров неочевидных моментов при использовании LINQ to SQL. Если вы гуру .NET, вам, возможно, покажется это скучным, остальным — добро пожаловать!
Начнем с такого примера. Допустим, у нас есть сущность «тип действия». У типа действия есть human-readable имя и системное имя — некий уникальный идентификатор, по которому с объектами этой сущности мы сможем работать из кода. Вот такая структура в виде объектов в коде:

class ActionType
{
	public int id;
	public string systemname;
	public string name;
}

var ActionTypes = new ActionType[] {
	new ActionType {
		id = 1,
		systemname = "Registration",
		name = "Регистрация"
	},
	new ActionType {
		id = 2,
		systemname = "LogOn",
		name = "Вход на сайт"
	},
	new ActionType {
		id = 3,
		systemname = null,
		name = "Некоторый тип действия без системного имени"
	}
};

Для такой же структуры с аналогичными данными создана таблица в БД и вспомогательные объекты для использования LINQ to SQL. Допустим, нам необходимо выяснить, существует ли у нас тип действия с системным именем NotExistingActionType. Вопрос в том, что будет выведено на экран после выполнения этих инструкций:

var resultForObjects = ActionTypes.All(actionType => actionType.systemname != "NotExistingActionType");
var context = new LinqForHabr.DataClasses1DataContext();
var resultForLTS = context.ActionTypes.All(actionType => actionType.SystemName != "NotExistingActionType");

Console.WriteLine("Result for objects: " + resultForObjects + "\nResult for Linq to sql: " + resultForLTS);
Console.ReadLine();

SQL Server хранит информацию обо всех объектах и их свойствах в виде метаданных, доступ к которым возможен через системные представления. Кроме того, некоторые из системных представлений скрывают в себе интересные нюансы, позволяющие лучше понять как устроена DBMS.

Чтобы просмотреть тело системного преставления, как впрочем и любого другого скриптового объекта, применяют функцию – OBJECT_DEFINITION:

PRINT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))

Однако, у OBJECT_DEFINITION, также как и у ее аналога sp_helptext, есть существенный недостаток – с их помощью нельзя вернуть скриптовое описание для табличного объекта.

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL
  DROP TABLE dbo.Table1
GO

CREATE TABLE dbo.Table1 (ColumnID INT PRIMARY KEY)
GO

EXEC sys.sp_helptext 'dbo.Table1'
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.Table1', 'U'))

При выполнении sp_helptext мы получим ошибку:

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 107
There is no text for object 'dbo.Table1'.

При тех же условиях, системная функция OBJECT_DEFINITION вернет NULL.

Также не решит проблемы выборка из sys.sql_modules, поскольку внутри этого системного представления используется все тот же вызов функции OBJECT_DEFINITION:

CREATE VIEW sys.sql_modules AS
    SELECT object_id = o.id,
        definition = object_definition(o.id),
        ...
    FROM sys.sysschobjs o

Такое поведение весьма печально, поскольку для некоторых сценариев, бывает полезно получить скриптовое описание таблицы. Что ж, заглянем в системные представления и создадим аналог функции OBJECT_DEFINITION для работы с табличными объектами.

В относительно недавнем прошлом, возникла задача автоматизировать процесс генерации и рассылки HTML отчетов руководству по продажам за текущий месяц. Так уж вышло, что для каждого руководящего лица создавались отдельные таблицы с необходимой только им информацией.

Поскольку, для каждого отчета, все делалось вручную, что, мягко говоря, было нерациональным.

Было решено генерировать HTML со стороны сервера базы данных и через Database Mail формировать рассылку путем выполнения команды sp_send_dbmail.

Большинство примеров, приведенный в Сети, создавали разметку вручную — это было не слишком эффективным подходом. При этом я не нашел универсального решения, позволяющего работать с таблицой имеющей произвольную структуру.

Чтобы заполнить этот пробел предлагаю на рассмотрение мой вариант решения.

Вы, возможно, помните как несколько лет назад стремительно стали набирать популярность NoSQL-базы данных (MongoDB, DynamoDB и другие). Многие пророчили смерть классических реляционных баз данных, торжество новых парадигм и всеобщее счастье в мире. И вы, возможно, в курсе того, как в последний год (или около того) наблюдается откат этой эйфории — выходят статьи типа «Broken by Design: MongoDB Fault Tolerance» и Why You Should Never Use MongoDB. Народ на Хабре на Тостере интересуется — «А почему же Монгу критикуют?», на что получает ответы «перерекламировали», «серебрянной пули нет», «надо выбирать базу данных по задачам».

Все 3 очевидных варианта — «Использовать реляционную БД», «Использовать NoSQL-БД», «Выбирать БД по задачам проекта» мне не нравятся по причине, высказанной в заголовке статьи.

Постигшие дзэн Пайтона считают, что должен быть один (и, желательно, только один) очевидный способ достигнуть желаемого.

А постигшие список модулей Node.js могут убедиться в том, что создатели этих модулей духовно ближе не к дзэн-буддистам, а к байхуистам — к поклонникам движения «Байхуа юньдун» (百花运动), провозглашённого Мао Цзэдуном в 1957 году по мотивам классического китайского стихотворения «пусть расцветают сто цветов, пусть соперничают сто школ», начинающегося словами «бай хуа» («百花», «сто цветов»). Иными словами, модули для Node.js предоставляют, как правило, несколько способов сделать одно и то же, и из них потребитель выбирает тот способ, который более всех пригоден ему.

Но почему не существует такого одного способа, который был бы пригоден для всех?

Ответ на этот вопрос я предлагаю рассмотреть на примере употребления базы данных SQLite.

На практике, задачи по объединению строк в одну попадаются достаточно часто. Весьма печально, но стандарт T-SQL не предусматривает возможности использовании строковых данных внутри агрегирующей функции SUM:

Msg 8117, Level 16, State 1, Line 1
Operand data type char is invalid for sum operator.

Хотя для решения подобного рода задач, для MySQL была добавлена функция GROUP_CONCAT, а в Oracle – LISTAGG. В свою же очередь, SQL Server такого встроенного функционала пока не имеет.

Однако, не стоит рассматривать это как недостаток, поскольку возможности T-SQL позволяют выполнять конкатенации строк более гибко и эффективно за счет применения других конструкций, которые будут рассмотрены далее.

Сегодня все большую популярность завоевывают In-Memory BI решения. Кубы уже не в моде, их структура морально устарела, и хотя они довольно прилично масштабируются, требования к скорости работы современных BI систем значительно возросли. Тем не менее, многие компании до сих пор успешно используют аналитику, построенную на одном из OLAP-серверов (Microsoft, Oracle, Cognos, и др.). Мне, например, очень нравится Microsoft SQL Server Analysis Service, и я хотел бы рассказать, как в нем можно использовать немного необычную для аналитики функцию – обратную запись данных в источник (Write Back).

Синтаксис конструкции INSERT может показаться весьма тривиальным, поскольку стандарт T-SQL рассматривал ключевое слово VALUES лишь в контексте вставки данных – INSERT INTO … VALUES ….

С выходом SQL Server 2008 существенно расширился синтаксис T-SQL, благодаря чему стало возможным использовать многострочную конструкцию VALUES, при этом не только в контексте вставки.

В данном топике будет рассмотрена сравнительная эффективность использования конструкции VALUES в различных типовых ситуациях. Чтобы дать объективную оценку полученных результатов, для каждого примера, будет рассмотрен его план выполнения.

За время моей работы, я сталкивался с широким кругом задач. Одни задачи требовали монотонной работы, другие сводились к чистому креативу.

Наиболее интересные задачи, которые я могу сейчас вспомнить, так или иначе, затрагивали вопросы оптимизации запросов.

Оптимизация – это, в первую очередь, поиск оптимального плана запроса. Однако, что делать в ситуации, когда стандартная конструкция языка выдает план, который очень далек от оптимального?

С такого рода проблемой я столкнулся, когда применял конструкцию UNPIVOT для преобразования столбцов в строки.

Путем небольшого сравнительного анализа, для UNPIVOT была найдена более эффективная альтернатива.

Приветствую уважаемых читателей.
Данный материал прольет свет на проблему удобства работы с РСУБД, которой я посвятил много лет, но никак не находил времени рассказать.

Если вы не занимаетесь поиском, просмотром и анализом данных или же делаете это, но полностью удобно и не имеете ни в чем нужды, смело бросайте чтение данного текста.

Проблематика

Итак, вы – пользователь, имеющий право на чтение в некой СУБД. Вероятно, перед вами стоит набор типовых подзадач:

• Разобраться со структурой данных
• Найти в ней нужные сущности
• Найти в них нужные поля
• Найти связи между сущностями
• Найти интересующие значения
• Отобрать набор значений
• Выбрать нужные данные
• Убедиться, что это действительно ТЕ САМЫЕ данные, которые вы искали
• Сохранить результаты
• Подготовить из них отчеты

Наконец, весьма вероятно, что эти задачи вам надо решать регулярно.

На рынке инструментов обработки данных представлено огромное количество средств, посвященных построению запросов, кубов и отчетов. Прискорбно, но большая часть из них не видит наличия у пользователя вышеперечисленных задач во всей их полноте. Перечислим типичные проблемы, в обратном порядке относительно предыдущего списка:

Широко известна фраза: «Повторение – мать учения». Возможно, это звучит банально, но на втором году работы, я смог в полной мере прочувствовать смысл этой фразы.

С одной стороны, когда человек открывает для себя что-то новое, повторение пройденного, в разумных пределах, позволяет ему лучше закрепить материал. Однако, в моей ситуации, ежедневно приходилось решать функционально схожие задачи. Закономерный результат — плавное снижение мотивации делать это вручную.

Найти выход, из сложившейся ситуации, мне помог динамический SQL, который позволил автоматизировать наиболее рутинные операции и повысить производительность труда.

Далее приведено несколько примеров из жизни, которые решались посредством применения динамического SQL.

Мы работаем над DWH в телекоммуникациях, поэтому пример, который я рассматриваю, называется «Абонент». Принцип универсален и это мог быть «Клиент» или «Пациент» — в зависимости от отрасли. Я надеюсь методику найдут полезной разработчики DWH из разных отраслей.

Если Вы не понимаете, что такое DWH, измерения и факты, я рекомендую прочитать книгу Ральфа Кимбалла «Dimensional Modeling». Речь идёт о базе данных для аналитики и консолидированной отчетности предприятия, конкретно о формировании и актуализации измерений — таблиц, которые хранят атрибуты (поля) для отбора (WHERE) в будущих запросах.

Хочу поделиться опытом создания механизма генерации большой базы данных товаров. С его помощью наши пользователи могут за несколько минут сгенерировать более миллиона однотипных, но разных записей.