Архив метки: SQL

SQL Server 2012. Несколько способов динамической сортировки

Добрый день!
Сегодня, изучая новые возможности SQL Server 2012, я поэкспериментировал с несколькими способами динамической сортировки.

Допустим мы имеем таблицу

table1
(
 ID INT,
 Name1 varchar(100),
 Name2 varchar(100),
 Name3 varchar(100)
)

И нам необходимо по какому-то признаку сортировать данные при выборке из таблицы по разным столбцам

Читать далее

Postgresql. Преобразовываем колонку в строку (сolumn to row)

Я не раз возвращался к подобной теме для других СУБД.

Сегодня покажу пример для Postgresql:

select array_to_string(ARRAY(select 'Тракторист Вася' union select 'Тракторист Петя'),',')

где

1) select ‘Тракторист Вася’ union select ‘Тракторист Петя’ — интерпретация табличного набора из одной колонки;

2) ARRAY — формирует массив данных из табличного набора.

3) array_to_string — преобразовывает массив в строку с разделителем (в моем случае это запятая).

На выходе получаем:
Тракторист Вася, Тракторист Петя

MS SQL. Удаляем дупликаты с помощью оконных функций.

Допустим, что перед нами стоит задача удалить дубликаты из таблиц(ы).

Это можно сделать следующим образом.

Допустим у нас есть некая таблица (учитывая мою «симпатию к трактористам»):

CREATE TABLE tbl_List_of_Tractorist (T_ID int identity, T_Name varchar(50))

Наполняем таблицу. В том числе генерим дупликаты.

INSERT INTO tbl_List_of_Tractorist
VALUES('Vasya'), ('Petya'), ('Ivan'), ('Petya'), ('Ivan'), ('Vasya')
, ('Petya'), ('Ivan')

Как видим у нас 3 Ивана, 3 Петра и 2 Василия.

Из этого всего надо оставить каждого имени по-одному.
Делаем следующее:

;WITH CTE
	AS
	(
	    SELECT T_ID, T_Name, ROW_NUMBER() OVER(partition by T_Name Order by T_ID) As RowNum
	    FROM tbl_List_of_Tractorist
	)
DELETE from CTE Where RowNum>1

Но стоит быть весьма осторожным с таблицами на которых навешано n-ое количество индексов или есть тригеры или же количество записей очень большое.
Иногда дешевле будет выполнить Bulk-Insert (SELECT ….. INTO …..) в другую таблицу, которая будет хранить уже отобранные данные, очистить первую и залить данные обратно.

Вычисление наибольшей(наименьшей) зарплаты сотрудника без TOP.

Пример вычисления наибольшей(наименьшей) зарплаты сотрудника:
— 3 наибольших зарплаты.

select top 3 * from tbl_employee order by employee_salary desс

— 3 наименьших зарплаты.

select top 3 * from tbl_employee order by employee_salary asc

 

Вот тоже интересный вариант:

— рейтинг наименьших зарплат

WITH employee_rank
AS (SELECT employee_key,
Row_number() OVER(ORDER BY employee_salary ASC) AS [Salary_Rank]       FROM tbl_employee

)
SELECT *
FROM employee_rank
WHERE [Salary_Rank] IN ( 1,
2, 3 )

— рейтинг наибольших заплат

 

WITH employee_rank
AS (SELECT employee_key,
Row_number() OVER(ORDER BY employee_salary DESC) AS [Salary_Rank]       FROM tbl_employee

)
SELECT *
FROM employee_rank
WHERE [Salary_Rank] IN ( 4,
5, 6 )

 

Скрипт для получения доступного и свободного дискового пространстава для SQL сервера

Мы можем проверить место на диске путем выполнения сценария PowerShell с помощью XP_CMDShell от ВСС. Для того чтобы сделать это, вам нужно убедиться, что XP_CMDSHELL включен для Вашего SQL сервера.

Вы можете выполнить сценарий ниже для проверки и включить XP_Cmdshell. Чтобы включить XP_Cmdshell Вы должны по крайней мере иметь разрешение ALTER SETTINGS на уровне сервера. Разрешение ALTER SETTINGS неявно предоставлены sysadmin и serveradmin фиксированных серверных ролей.

Oracle. Колонка в строку

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

Вот пример

 

select wmsys.wm_concat (a.val)
(select 1 as val from dual
union
select 2 as val from dual ) a

 

Eсли Вам понадобится уникальное значение, то можно использовать и вот так wmsys.wm_concat(distinct <Ваше поле>)

Или вот еще пример использования. В селе есть два тракториста: Вася и Петя. Их трактора обрабатывают некие поля которые проидентифицированы. Нам надо узнать на каких полях Вася и Петя вкалывают:

select tractorist_id, wmsys.wm_concat(distinct <pole_id>) as list_of_pole
from <таблцица соотношений тракторист-поле>
group by tractorist_id

Как результат получим, что-то типа такого:

tractorist_id          list_of_pole
——————————————
1                               1,4,8,10
2                               2,3,9,11

Трассировка на стороне SQL сервера – Часть 2 (Stairway. Tracing Step 2: Creating a SQL Trace Using T-SQL)

Узнайте, как использовать подсистему трассировки sql в sql server, для проверки ваших экземпляров. Эта серия будет изучать основы sql трассировки и научит вас, как настроить, планировать и управлять трассировкой.

Ранее в первой лекции я упоминал о том, что server side трассировка требует гораздо меньше ресурсов чем profiler трассировка. Этот раздел покажет вам как при помощи TSQL создать server side трассировку с минимальной нагрузкой на сервер.

Читать далее

Insert даты с последней секундой дня или как я лоханулся

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

Мне необходимо было вставить пару строк с последними секундами дня. Т.е. что-то типа

INSERT INTO mytab (date_col) VALUES('2011-05-31 23:59:59')

Каково же было мое удивление, когда при select из моей таблицы я получил ‘2011-06-01 00:00:00’ вместо ‘2011-05-31 23:59:59’.

Нет бы сразу посмотреть на тип поля )))) А он оказался smalldatetime.

А у него есть особенность с округлением. Значения, меньшие или равные 29,998 секунд, округляются до минуты в меньшую сторону; значения, равные или большие 29,999, округляются до минуты в большую сторону.

Т.е. получить значение ‘2011-05-31 23:59:59’ у меня не получится. Но получится хотя бы вставить ‘2011-05-31 23:59:00’.

INSERT INTO mytab (date_col) VALUES('2011-05-31 23:58:31')

SQL — Преобразование содержимого колонки в строку (column to row)

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

Не буду описывать свою структуру, а просто покажу на примере.

В качестве базы данных используется MS SQL 2008.

Читать далее

5 первых SQL ошибок, которые должны быть проверены

Как тренер и преподаватель, использующий t-sql, я вижу ошибки, повторяющиеся снова и снова. Это вдохновило меня описать 5 из них наиболее повторяющихся, чтобы не допускать их в дальнейшем.

Общая SQL ошибка №1 — большое или малое количество запятых
Это— наиболее распространенная ошибка, написанная на SQL. Следующий запрос является типичным, содержащим дополнительную запятую:

Читать далее