SQL скрытые методы?

Ответ 1

SQL GROUP BY - Предложения CUBE, ROLLUP

Аналитические (функции AKA, AKA windowing) IE:

• ROW_NUMBER
• RANK
• DENSE_RANK
• NTILE
• OVER

Просмотры: нормальный и материализованный

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

Ответ 2

Предложение OVER (SQL Server) aka Функции окна (PostgreSQL) или аналитические функции (Oracle)

Это было очень приятно узнать для меня. Вы можете делать всевозможные полезные вещи, такие как подсчет, разбиение на разделы, ранжирование и т.д.

Ответ 3

EXISTS. Я поражен тем, сколько людей по-прежнему используют COUNT (*) при проверке существования или предложениях IN (SELECT...), когда EXISTS могут выполнять работу намного быстрее.

Чаще всего вы можете увидеть:

SELECT @MyVar = Count(*) FROM Table1 WHERE....
If @MyVar <> 0
BEGIN
   --do something
END

когда

IF EXISTS(SELECT 1 FROM Table1 WHERE...)
BEGIN
    --don something
END

всегда лучше.

Ответ 4

SELECT... EXCEPT SELECT...

и

SELECT... INTERSECT SELECT...

может быть полезным (и тревожно эффективным) при выделении разных или общих строк - и что для всех столбцов в строке - между множествами. Это очень полезно, когда у вас много столбцов.

Ответ 5

Люди не используют встроенные функции достаточно и любят изобретать колесо, вот Десять функций SQL Server, которые вы проигнорировали до сих пор

Использование NEWSEQUENTIALID() вместо NEWID() в кластерном столбце uniqueidentifier будет работать намного лучше, поскольку оно не приведет к разбиению страниц и, следовательно, фрагментации

Использование таблицы чисел с добавлением, чтобы вы могли быстро выполнить некоторую основанную на наборе логику

например

select DATEADD(m,number,'20010101')
from master..spt_values
where type = 'P'
order by 1

ЛЮБЫЕ, ВСЕ И НЕКОТОРЫЕ

Ответ 6

Два из Postgresql: DISTINCT ON (см. пример) и новый WITH.

Ответ 7

В последнее время я часто использовал CROSS APPLY.

Ответ 8

Pivot

Это новое в 2005 году (которое я знаю давно, но там много людей, которые все еще используют 2000). Сохраняет выполнение кучи "случая, когда name =" tim ", а затем значение else 0 end", чтобы построить ваши агрегаты в эти выходные.

Ответ 9

Общие выражения таблицы (SQL Server 2005 +)

WITH x AS (
    SELECT 1 as A, 2 as B, 3 as C
),
WITH y AS (
    SELECT 4 as A, 5 as B, 6 as C
    UNION
    SELECT 7 as A, 8 as B, 9 as C
)
SELECT A, B, C FROM x
UNION
SELECT A, B, C FROM y

Они действительно хороши для разбивки ваших запросов на шаги

Ответ 10

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

Мне пришлось делать это, имея дело с данными из 10+ миллионов записей, соединенных примерно с 15 + справочными таблицами. Без прямого соединения система подавлялась через 20 часов. Добавление Straight-join, это было сделано примерно через 2 часа.

Ответ 11

В Sql Server - предложение HAVING. В частности, HAVING (COUNT DISTINCT FOO) > @SomeNumber для быстрого поиска строк с более чем одним отдельным значением для данной группировки.

От MSDN:

USE AdventureWorks2008R2 ;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(LineTotal) > 100000.00
ORDER BY SalesOrderID ;

Ответ 12

В PostgreSQL Docs:

Разделение таблиц

Разделение означает разделение логически одной большой таблицы на более мелкие физические части. Разделение может обеспечить несколько преимуществ:

• Производительность запроса может быть значительно улучшена для определенных типов запросов.

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

• Массовое удаление может быть выполнено простым удалением одного из разделов, если это требование запланировано в конструкцию разбиения. DROP TABLE намного быстрее, чем объемный DELETE, не говоря уже о следующих накладных расходах VACUUM.

• Редко используемые данные могут быть перенесены на более дешевые и медленные носители.

Ответ 13

Производные таблицы для создания "переменных" и сокращения повторяющегося кода.

Что-то вроде этого, но может быть расширено. Очевидно, что "Среднее значение" может быть гораздо более сложным вычислением, и если у вас есть несколько, это помогает очистить код.

Select *, case when AverageValue > 50 then 'Pass' Else 'Fail' end
From
(
 Select ColA, ColB, AverageValue = (ColA+ColB)/2
 From InnerMostTable
) AverageValues
Order By AverageValue Desc

Ответ 14

В SQL Server с помощью функции Convert() для получения дат в формате mm/dd/yyyy вместо функции Cast()

SELECT convert(datetime,  '1/1/2010', 101)

Я использую это все время