Ответ 1

Действительно ли вы протестировали это решение перед тем, как отклонить его?

SELECT
    (SELECT TOP(1) col1 FROM Table1 WHERE col1 IS NOT NULL ORDER BY SortCol) AS col1,
    (SELECT TOP(1) col2 FROM Table1 WHERE col2 IS NOT NULL ORDER BY SortCol) AS col2,
    (SELECT TOP(1) col3 FROM Table1 WHERE col3 IS NOT NULL ORDER BY SortCol) AS col3

Если это медленно, возможно, потому, что у вас нет соответствующего индекса. Какие индексы у вас есть?

Ответ 2

Проблема с реализацией этого как агрегации (которую вы действительно могли бы сделать, если бы, например, вы реализовали агрегат SQL Server "First-Non-Null" ), это потерянное IO для чтения каждой строки, когда вы, как правило, только заинтересованы в первых нескольких строках. Агрегация не будет останавливаться только после первого ненулевого значения, даже если его реализация игнорирует дальнейшие значения. Агрегации также неупорядочены, поэтому ваш результат будет зависеть от упорядочения индекса, выбранного механизмом запросов.

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

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

Ниже приведены некоторые результаты для разных размеров таблиц:

Rows  Cols  Aggregation IO  CPU  Subquery IO  CPU
3     3                 2   0             6   0
1728  3                 8   63            6   0
1728  8                 12  266           16  0

Здесь измеряется IO - количество логических чтений. Обратите внимание, что количество логических чтений для подзапроса не меняется с количеством строк в таблице. Также имейте в виду, что логические чтения, выполняемые каждым дополнительным подзапросом, скорее всего будут для тех же страниц данных (содержащих первые несколько строк). Агрегация, с другой стороны, должна обрабатывать всю таблицу и требует некоторого времени процессора для этого.

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

Определение таблицы и вставка тестовых данных:

CREATE TABLE Table1 (Col1 int null, Col2 int null, Col3 int null, SortCol int);
CREATE CLUSTERED INDEX IX_Table1 ON Table1 (SortCol);

WITH R (i) AS
(
 SELECT null

 UNION ALL

 SELECT 0

 UNION ALL

 SELECT i + 1
 FROM R
 WHERE i < 10
)
INSERT INTO Table1
SELECT a.i, b.i, c.i, ROW_NUMBER() OVER (ORDER BY NEWID())
FROM R a, R b, R c;

Запрос таблицы:

SET STATISTICS IO ON;

--aggregation
SELECT TOP(0) * FROM Table1 --shortcut to convert columns back to their types
UNION ALL
SELECT
 dbo.FirstNonNull(Col1),
 dbo.FirstNonNull(Col2),
 dbo.FirstNonNull(Col3),
 null
FROM Table1;


--subquery
SELECT
    (SELECT TOP(1) Col1 FROM Table1 WHERE Col1 IS NOT NULL ORDER BY SortCol) AS Col1,
    (SELECT TOP(1) Col2 FROM Table1 WHERE Col2 IS NOT NULL ORDER BY SortCol) AS Col2,
    (SELECT TOP(1) Col3 FROM Table1 WHERE Col3 IS NOT NULL ORDER BY SortCol) AS Col3;

Тест CLR "first-non-null" для тестирования:

 [Serializable]
 [SqlUserDefinedAggregate(
  Format.UserDefined,
  IsNullIfEmpty = true,
  IsInvariantToNulls = true,
  IsInvariantToDuplicates = true,
  IsInvariantToOrder = false, 
#if(SQL90)
  MaxByteSize = 8000
#else
  MaxByteSize = -1
#endif
 )]
 public sealed class FirstNonNull : IBinarySerialize
 {
  private SqlBinary Value;

  public void Init()
  {
   Value = SqlBinary.Null;
  }

  public void Accumulate(SqlBinary next)
  {
   if (Value.IsNull && !next.IsNull)
   {
    Value = next;
   }
  }

  public void Merge(FirstNonNull other)
  {
   Accumulate(other.Value);
  }

  public SqlBinary Terminate()
  {
   return Value;
  }

  #region IBinarySerialize Members

  public void Read(BinaryReader r)
  {
   int Length = r.ReadInt32();

   if (Length < 0)
   {
    Value = SqlBinary.Null;
   }
   else
   {
    byte[] Buffer = new byte[Length];
    r.Read(Buffer, 0, Length);

    Value = new SqlBinary(Buffer);
   }
  }

  public void Write(BinaryWriter w)
  {
   if (Value.IsNull)
   {
    w.Write(-1);
   }
   else
   {
    w.Write(Value.Length);
    w.Write(Value.Value);
   }
  }

  #endregion
 }

Ответ 3

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

create table Foo (data1 tinyint, data2 tinyint, data3 tinyint, seq int not null)
go

insert into Foo (data1, data2, data3, seq)
values (NULL, 4, 8, 1), (1, NULL, 0, 2), (5, 7, NULL, 3)
go

with unpivoted as (
    select seq, value, col
    from (select seq, data1, data2, data3 from Foo) a
    unpivot (value FOR col IN (data1, data2, data3)) b
), firstSeq as (
    select min(seq) as seq, col
    from unpivoted
    group by col
), data as (
    select b.col, b.value
    from firstSeq a
    inner join unpivoted b on a.seq = b.seq and a.col = b.col
)
select * from data pivot (min(value) for col in (data1, data2, data3)) d
go

drop table Foo
go

Ответ 4

Вот еще один способ сделать это. Это будет наиболее полезно, если ваша база данных запрещает верхний (N) в подзапросах (например, мой, Teradata).

Для сравнения, здесь решение, упомянутое другими людьми, используя top(1):

select top(1) Col1 
from Table1 
where Col1 is not null 
order by SortCol asc

В идеальном мире это кажется мне лучшим способом сделать это - чистым, интуитивным, эффективным (по-видимому).

В качестве альтернативы вы можете сделать это:

select max(Col1) -- max() guarantees a unique result
from Table1 
where SortCol in (
    select min(SortCol) 
    from Table1 
    where Col1 is not null
)

Оба решения извлекают "первую" запись вдоль упорядоченного столбца. top(1) делает это более элегантно и, вероятно, более эффективно. Второй метод делает то же самое концептуально, только с более ручной/явной реализацией с точки зрения кода.

Причиной для max() в выборе корня является то, что вы можете получить несколько результатов, если значение min(SortCol) отображается в более чем одной строке в Table1. Я не уверен, как top(1) обрабатывает этот сценарий, кстати.

Ответ 5

Как первое и второе ненулевое значение из одной группы col по идентификатору

Ответ 6

Использование first_value()

first_value(col) может использоваться с and OVER (ORDER BY CASE WHEN col IS NOT NULL THEN sortcol ELSE maxvalue END). ELSE maxvalue требуется, потому что SQL Server сначала сортирует пустые значения)

CREATE TABLE foo(a int, b int, c int, sortCol int);
INSERT INTO foo VALUES
    (null, 4, 8, 1),
    (1, null, 0, 2),
    (5, 7, null, 3);

Теперь вы можете увидеть, что мы должны сделать, чтобы заставить нули сортироваться после sortcol. Чтобы сделать desc, вы должны убедиться, что они имеют отрицательное значение.

SELECT TOP(1)
     first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS a,
     first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS b,
     first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol ELSE 2^31-1 END) AS c
FROM foo;

PostgreSQL

PostgreSQL немного проще,

CREATE TABLE foo(a,b,c,sortCol)
AS VALUES
  (null, 4, 8, 1),
  (1, null, 0, 2),
  (5, 7, null, 3);

SELECT
     first_value(a) OVER (ORDER BY CASE WHEN a IS NOT NULL THEN sortcol END) AS a,
     first_value(b) OVER (ORDER BY CASE WHEN b IS NOT NULL THEN sortcol END) AS b,
     first_value(c) OVER (ORDER BY CASE WHEN c IS NOT NULL THEN sortcol END) AS c
FROM foo
FETCH FIRST ROW ONLY;

Я полагаю, что все это исчезнет, когда РСУБД начнут внедрять IGNORE NULLS. Тогда это будет просто first_value(a IGNORE NULLS).