Создайте псевдостолбец для отображения Да/Нет, если одно значение столбца присутствует в другом для определенного идентификатора

У меня есть таблица со следующей структурой:

create table test_18Nov ( account_id nvarchar(12)
                        , account_name nvarchar(25)
                        , zip_legacy_file nvarchar(5)
                        , Region_legacy_file nvarchar(30)
                        , zip_new_source nvarchar(5)
                        , Region_new_source nvarchar(30)
                        )

INSERT INTO test_18Nov VALUES ('S1018', 'John Smith',       '32221', 'R087-Jacksonville',   '33803', 'R026-Lakeland')
INSERT INTO test_18Nov VALUES ('S1018', 'John Smith',       '33606', 'R011-Tampa',          '32220', 'R087-Jacksonville')
INSERT INTO test_18Nov VALUES ('S1018', 'John Smith',       '33803', 'R026-Lakeland',       '33606', 'R011-Tampa')
INSERT INTO test_18Nov VALUES ('AC054', 'David Thompson',   '33606', 'R011-Tampa',          '32205', 'R087-Jacksonville')
INSERT INTO test_18Nov VALUES ('AC054', 'David Thompson',   '33870', 'R058-Sebring',        '33606', 'R011-Tampa')
INSERT INTO test_18Nov VALUES ('AC054', 'David Thompson',   '33610', 'R011-Tampa',          '33870', 'R058-Sebring')
INSERT INTO test_18Nov VALUES ('AC077', 'Stacey Leigh',     '34950', 'R043-Fort Pierce',    '34982', 'R043-Fort Pierce')
INSERT INTO test_18Nov VALUES ('AC077', 'Stacey Leigh',     '33610', 'R011-Tampa',          '34950', 'R043-Fort Pierce')

Я должен создать псевдостолбец для всех строк с Да или Нет. Для идентификатора учетной записи, если устаревший регион (или регионы) присутствует в новом исходном регионе (или регионах), то в псевдостолбце будет отображаться «Нет». Если для учетной записи новый исходный регион (или регионы) отсутствует в устаревшем регионе (или регионах), то учетная запись будет считаться перемещенной, и в псевдостолбце будет отображаться «Да». В приведенном выше наборе данных AC054 является единственным идентификатором учетной записи, который должен иметь значение псевдостолбца «Да», поскольку R087-Jacksonville (новый исходный регион) отсутствует в списке устаревших регионов.

Ожидаемый результат должен быть:

account_id  | account_name      |  Region_legacy_file   | Region_new_source     | Will the account move?    |
-------------------------------------------------------------------------------------------------------------
S1018       | John Smith        | R087-Jacksonville     | R026-Lakeland         |       No                  |
-------------------------------------------------------------------------------------------------------------
S1018       | John Smith        | R011-Tampa            | R087-Jacksonville     |       No                  |
-------------------------------------------------------------------------------------------------------------
S1018       | John Smith        | R026-Lakeland         | R011-Tampa            |       No                  |
-------------------------------------------------------------------------------------------------------------
AC054       | David Thompson    | R011-Tampa            | R087-Jacksonville     |       Yes                 |
-------------------------------------------------------------------------------------------------------------
AC054       | David Thompson    | R058-Sebring          | R011-Tampa            |       Yes                 |
-------------------------------------------------------------------------------------------------------------
AC054       | David Thompson    | R011-Tampa            | R058-Sebring          |       Yes                 |
-------------------------------------------------------------------------------------------------------------
AC077       | Stacey Leigh      | R043-Fort Pierce      | R043-Fort Pierce      |       No                  |
-------------------------------------------------------------------------------------------------------------
AC077       | Stacey Leigh      | R011-Tampa            | R043-Fort Pierce      |       No                  |
-------------------------------------------------------------------------------------------------------------

Я подумал об использовании предложения NOT EXISTS, но это вернет только строки, в которых новый исходный регион не найден в списке устаревших регионов для идентификатора учетной записи, что мне не поможет в этой ситуации. Единственный способ, который я могу придумать, - это использовать CASE WHEN EXISTS, но я не смог заставить его работать. Если есть другой способ, поделитесь.

РЕДАКТИРОВАТЬ : Почему в AC054 псевдостолбец имеет значение «Да»? - Есть 3 новых исходных региона для учетной записи с идентификатором AC054: R087, R011 и R058. Из этих 3 регионов 2 региона находятся в устаревших регионах для идентификатора учетной записи AC054, и это R011 и R058. Таким образом, R087 не является частью списка устаревших регионов для идентификатора учетной записи AC054, и, следовательно, псевдостолбец имеет значение «Да». Для двух других идентификаторов учетных записей это не так, потому что все новые исходные регионы находятся в списке устаревших регионов.

честно говоря, я до сих пор не могу понять ваши критерии истинности и ложности

nbk 18.11.2022 19:46

Если он работает с одной строкой, а не со всей учетной записью, используйте оконные функции, чтобы распространить результат на оставшиеся строки. MAX(your_expression) OVER (PARTITION BY account_id) AS move_yes_no

MatBailie 18.11.2022 19:48

@nbk — если вы посмотрите на данные для идентификатора учетной записи AC054, вы заметите, что оба устаревших региона присутствуют в новых исходных регионах. Но R087 из нового исходного региона отсутствует в устаревшем регионе. Это все еще не ясно?

Arty155 18.11.2022 20:56

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

nbk 18.11.2022 21:01

@nbk - Другой таблицы нет и нет географической привязки. Я только что привел пример. Значения также могут быть «Регион 1-ABC», «Регион 2-TGV», «Регион 3» или любая комбинация символов, которую только можно придумать. Логика заключается в том, что для определенного идентификатора учетной записи новый исходный регион не должен присутствовать в устаревшем регионе. Я отредактировал ожидаемый результат в своем вступительном посте. Надеемся, что меньшее количество столбцов может помочь в визуализации данных.

Arty155 18.11.2022 21:08

@MatBailie - мой запрос не дает результатов, которые я разместил здесь. Я опубликую свой запрос, но я хотел бы повторить еще раз: запрос не дает желаемого результата. select o.*, CASE WHEN EXISTS (select 1 from test_18Nov i where i.account_id = o.account_id and i.Region_new_source <> o.Region_legacy_file) THEN 'Yes' ELSE 'No' END as Will_the_account_move from test_18Nov o

Arty155 18.11.2022 21:28

@ Arty155 Итак, я предложил вам обернуть выражение CASE в оконную функцию, которую я вам дал.

MatBailie 18.11.2022 23:20
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
2
7
66
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Вы можете использовать подзапрос SQL, что означает, что для каждой строки вы выполняете другой запрос, который ищет результат, например:

SELECT name, id, location, (SELECT TOP 1 'exists' FROM location l WHERE l.location = c.location) 
FROM customer c

клиент c и местоположение l - это 2 разные таблицы. У меня всего одна таблица. Также самостоятельное присоединение выполняется по идентификатору учетной записи, но сравнение двух типов регионов - устаревшего и нового источника.

Arty155 18.11.2022 20:54

Это был пример; вы можете понять, как применить эту концепцию к вашему собственному запросу.

T. van Schagen 22.11.2022 22:56
Ответ принят как подходящий

Я проверяю вашу информацию и сделал пример того, какой подзапрос CheckLegacy вернет учетную запись, которую следует переместить, в следующем примере:

WITH test_18Nov  AS (
    SELECT * FROM (
    VALUES
        ('S1018', 'John Smith',       '32221', 'R087-Jacksonville',   '33803', 'R026-Lakeland'), 
        ('S1018', 'John Smith',       '33606', 'R011-Tampa',          '32220', 'R087-Jacksonville'), 
        ('S1018', 'John Smith',       '33803', 'R026-Lakeland',       '33606', 'R011-Tampa'), 
        ('AC054', 'David Thompson',   '33606', 'R011-Tampa',          '32205', 'R087-Jacksonville'), 
        ('AC054', 'David Thompson',   '33870', 'R058-Sebring',        '33606', 'R011-Tampa'),
        ('AC054', 'David Thompson',   '33610', 'R011-Tampa',          '33870', 'R058-Sebring'),
        ('AC077', 'Stacey Leigh',     '34950', 'R043-Fort Pierce',    '34982', 'R043-Fort Pierce'),
        ('AC077', 'Stacey Leigh',     '33610', 'R011-Tampa',          '34950', 'R043-Fort Pierce')
    ) AS _ (account_id,account_name, zip_legacy_file,Region_legacy_file,zip_new_source,Region_new_source)
),
--formatting the query for the field I need
IdAndNewLegacy as (
    SELECT account_id, Region_new_source FROM test_18Nov
),
--check if some new legacy region is not in Region_legacy_file
CheckLegacy as (
    SELECT I.account_id, T.account_id as id FROM IdAndNewLegacy as I
    LEFT JOIN test_18Nov as T ON I.account_id = T.account_id and I.Region_new_source = T.Region_legacy_file
    WHERE T.account_id is null
    GROUP BY I.account_id, T.account_id
)
--Query to present the data
SELECT 
    t.*,
    CASE WHEN c.account_id is not null then 'Yes' ELSE 'No' END as [Will the account move?]
FROM 
    test_18Nov  as t
    LEFT JOIN CheckLegacy as c ON T.account_id = C.account_id 

Результат:

С уважением

Спасибо @Диего! Это сработало, и это было быстро. Фактическая таблица, над которой я работаю, содержит почти 250 тыс. записей, и ваш запрос выполняется за 4-5 секунд. Моя искренняя благодарность вам. Я отметил это как ответ.

Arty155 18.11.2022 22:20

Не нужно включать T.account_id AS id или включать его в пункт GROUP BY. Кроме того, пожалуйста, не включайте данные или результаты в виде связанных изображений, это противоречит рекомендациям SO. Глянь сюда; Пожалуйста, не загружайте изображения кода/данных/ошибок.

MatBailie 19.11.2022 00:20

Используйте выражение CASE (или IIF()), чтобы получить ДА/НЕТ (используя проверку НЕ СУЩЕСТВУЕТ) для каждой строки.

Затем используйте MAX(expression) OVER (PARTITION BY account_id), чтобы дать всем строкам учетной записи YES, если ЛЮБАЯ из них да.

SELECT
  t.*,
  MAX(
    IIF(
      NOT EXISTS (
        SELECT *
          FROM Test_18Nov
         WHERE account_id         = t.account_id
           AND region_legacy_file = t.region_new_source
      ),
      'YES',
      'NO'
    ) 
  ) OVER (PARTITION BY t.account_id)
FROM
  test_18Nov   AS t
ORDER BY
  t.account_id,
  t.region_new_source

ДЕМО: https://dbfiddle.uk/rOS1OoFe

Спасибо Мэт! Это тоже сработало. Изначально я пытался пойти по этому пути, но не знал о функции IIF. Мне придется изучить функцию IIF. Спасибо, что поделились альтернативным способом достижения этого. Может ли пост иметь 2 ответа? Я хочу сделать это также решением моей проблемы.

Arty155 19.11.2022 08:27

@arty IIF(boolean, x, y) == CASE WHEN boolean THEN x ELSE y END с меньшим количеством символов. --- У вас не может быть двух принятых ответов, вы должны либо сохранить свой выбор, либо передумать.

MatBailie 19.11.2022 14:11

@arty Я также заметил, что у тебя есть EXISTS( WHERE = AND <> ), но это сильно отличается от NOT EXISTS ( WHERE = AND = )

MatBailie 19.11.2022 14:15

Другие вопросы по теме