Блог

Главная - Вопросы по программированию - Результаты сравнения двух операторов select в хранимой процедуре

Результаты сравнения двух операторов select в хранимой процедуре

#sql-server #stored-procedures #crystal-reports

#sql-сервер #хранимые процедуры #кристалл-отчеты

Вопрос:

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

Суть: клиенты моей организации проводят опрос при первом посещении и при каждом 6-м последующем посещении. Нам нужно знать, улучшился ли пользователь с течением времени. Способ, которым мы решили это сделать, — сравнить 1-й с самым последним. Таким образом, если они участвовали в 18 сеансах, сравниваются 1-й и 3-й опросы (потому что они завершили бы опрос 3 раза за 18 сеансов).

Мне удалось получить «первую» оценку и «последнюю» оценку с помощью двух сложных, многоуровневых вложенных операторов select внутри одной хранимой процедуры. «Первый» — это ссылка TOP (1) на уникальный идентификатор (DOCID), а затем упорядоченная по дате. «Недавний» — это ссылка TOP (1) на уникальный идентификатор (DOCID), а затем упорядоченная по убыванию даты. Это дает мне именно то, что мне нужно в каждом операторе, но оно не выводит то, что мне нужно правильно, что, очевидно, связано с порядком в операторах.

Конечным результатом будет создание с его помощью Crystal Report для целей отчетности о грантах.

 Declare 
@StartDate Date,
@EndDate Date,
@First_DOCID Int,
@First_Clientkey Int,
@First_Date_Screening Date,
@First_Composite_Score Float,
@First_Depression_Score Float,
@First_Emotional_Score Float,
@First_Relationship_Score Float,
@Recent_DOCID Int,
@Recent_Clientkey Int,
@Recent_Date_Screening Date,
@Recent_Composite_Score Float,
@Recent_Depression_Score Float,
@Recent_Emotional_Score Float,
@Recent_Relationship_Score Float,
@Difference_Composit_Score Float,
@Difference_Depression_Score Float,
@Difference_Emotional_Score Float,
@Difference_Relationship_Score Float

SET @StartDate = '1/1/2016'
SET @EndDate = '6/1/2016'

BEGIN
SELECT @First_DOCID = CB24_1.OP__DOCID, @First_Date_Screening = CB24_1.Date_Screening, @First_Clientkey = CB24_1.ClientKey, @First_Composite_Score = CB24_1.Composite_score, @First_Depression_Score = CB24_1.Depression_Results, @First_Emotional_Score = CB24_1.Emotional_Results, @First_Relationship_Score = CB24_1.Relationships_Results
FROM FD__CNSLG_BASIS24 AS CB24_1
WHERE (CB24_1.OP__DOCID =
        (Select TOP(1) CB24_2.OP__DOCID
        ...
        ORDER BY CB24_2.Date_Screening))
ORDER BY ClientKey DESC
END 

BEGIN
SELECT @Recent_DOCID = CB24_1.OP__DOCID, @Recent_Date_Screening = CB24_1.Date_Screening, @Recent_Clientkey = CB24_1.ClientKey, @Recent_Composite_Score = CB24_1.Composite_score, @Recent_Depression_Score = CB24_1.Depression_Results, @Recent_Emotional_Score = CB24_1.Emotional_Results, @Recent_Relationship_Score = CB24_1.Relationships_Results
FROM FD__CNSLG_BASIS24 AS CB24_1
WHERE (CB24_1.OP__DOCID =
                (Select TOP(1) CB24_2.OP__DOCID
                ...
                ORDER BY CB24_2.Date_Screening DESC))
ORDER BY ClientKey
END 

SET @Difference_Composit_Score = (@Recent_Composite_Score - @First_Composite_Score)
SET @Difference_Depression_Score = (@Recent_Depression_Score - @First_Depression_Score)
SET @Difference_Emotional_Score = (@Recent_Emotional_Score - @First_Emotional_Score)
SET @Difference_Relationship_Score = (@Recent_Relationship_Score - @First_Relationship_Score)

SELECT 
@First_DOCID AS First_Docid,
@First_Clientkey AS First_Clientkey,
@First_Date_Screening AS First_Date_Screening,
@First_Composite_Score AS First_Composite_Score,
@First_Depression_Score AS First_Depression_Score,
@First_Emotional_Score AS First_Emotional_Score,
@First_Relationship_Score AS First_Relationship_Score,
@Recent_DOCID AS Recent_DOCID,
@Recent_Clientkey AS Recent_Clientkey,
@Recent_Date_Screening AS Recent_Date_Screening,
@Recent_Composite_Score AS Recent_Composite_Score,
@Recent_Depression_Score AS Recent_Depression_Score,
@Recent_Emotional_Score AS Recent_Emotional_Score,
@Recent_Relationship_Score AS Recent_Relationship_Score,
@Difference_Composit_Score AS Difference_Composit_Score,
@Difference_Depression_Score AS Difference_Depression_Score,
@Difference_Emotional_Score AS Difference_Emotional_Score,
@Difference_Relationship_Score AS Difference_Relationship_Score

Комментарии:

1. Из вашего сообщения неясно, что такое нежелательное поведение … является ли OP__DOCID первичным ключом для FD__CNSLG_BASIS24?

2. Извините за это. Нежелательным поведением является 1) он выводит только 1 строку данных и 2) он не дает мне «первую» оценку и «последнюю» оценку от одного и того же клиента в той же строке. # 1 может быть связано с моим новичком, но я открыт для чтения и изучения. # 2 для меня очевидно, почему это происходит («первый» извлекает первую созданную строку, а «недавний» извлекает последнюю созданную строку, которая не будет одним и тем же клиентом). Я не уверен, что это способ связать ключ клиента. И да, OP__DocID является первичным ключом для каждой таблицы. Таким образом, FD__Cnslg_Basis24.OP__Docid является его первичным ключом.

Ответ №1:

В SQL вам не нужны ненужные объявленные переменные.

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

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.Client_Improvement_Results 
    (@StartDate DATETIME, @EndDate DATETIME)

AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- You would never do this in real-life but for a simple reproducible example...
    DECLARE  @Survey TABLE
    (
        Clientkey INT,
        Date_Screening DATE,
        Composite_Score FLOAT
    )

    INSERT INTO @Survey
    VALUES
        (1, '2014-04-01', 42.1),
        (1, '2014-04-10', 46.1),
        (1, '2014-04-20', 48.1),
        (2, '2014-05-10', 40.1),
        (2, '2014-05-20', 30.1),
        (2, '2014-05-30', 10.1)
    ;

    --Use Common Table Expression amp; Window Functions to ID first/recent visit by client
    WITH CTE AS (
        SELECT 
            S.Clientkey
            ,S.Composite_Score
            ,S.Date_Screening
            ,First_Date_Screening = MIN(S.Date_Screening) OVER(PARTITION BY S.Clientkey)
            ,Recent_Date_Screening = MAX(S.Date_Screening) OVER(PARTITION BY S.Clientkey)
        FROM @Survey AS S
    ) 

    --Self join of CTE with proper filters 
    --applied allows you to return differences in one row

    SELECT 
        f.Clientkey
        ,f.First_Date_Screening
        ,f.Recent_Date_Screening
        ,Difference_Score = r.Composite_Score - f.Composite_Score

    FROM
        CTE AS f --first
            INNER JOIN CTE AS r --recent
                ON f.Clientkey = r.Clientkey

    WHERE 
        f.Date_Screening = f.First_Date_Screening
        AND r.Date_Screening = r.Recent_Date_Screening

END
GO

Комментарии:

1. Извините за тупость, я снова собираюсь разыграть карту новичка.

2. Итак, вот мой недостаток знаний: я не уверен, что никогда не захочется делать в реальной жизни? ТАБЛИЦА Declare @Survey? Я также не уверен, что такое «С CTE». Я никогда не использовал и не видел OVER() Итак, позвольте мне немного почитать, и я вернусь и посмотрю, будет ли это иметь для меня больше смысла впоследствии. Не нужно, чтобы все здесь объясняли, что находится в сети 🙂 О, кстати, Джош. Причина, по которой я использую метод TOP (1) для получения первой и последней строки, а не MIN() / MAX(), заключается в том, что бывают случаи, когда в одну и ту же дату будет несколько записей (из-за законного ввода данных

3. (продолжение) или из-за дублирования данных). Использование Select TOP(1) DOCID, а затем упорядочивание его по дате позволяет избежать некоторых ошибок, с которыми мы сталкивались. Я открыт для других вариантов.

4. Обычно существует более одного способа что-то сделать, либо используя TOP 1, либо оконные функции, либо что-то еще. Большая рекомендация — избегать такого количества переменных, и это может быть достигнуто с помощью CTE. CTE является общим псевдонимом для выражения common-table. Вы можете дать ему более осмысленное имя. Я могу снова посетить завтра и дать вам что-то более полезное.

5. Спасибо, Джош! Прочитав о WITH и OVER, я многому научился. Я не понимал, что эти функции возбуждают, но вау, это делает жизнь намного приятнее!

Ответ №2:

Вот решение, которое я придумал после всех удивительных советов.Я хочу вернуться назад и заменить TOP(1) другой новой вещью, которую я узнал в какой-то момент:

 select pc.*
from (select pc.*, row_number() over (partition by Clientkey, ProgramAdmitKey order by Date_Screening) as seqnum
      from FD__CNSLG_BASIS24 PC) pc
where seqnum = 1

Однако сначала мне придется немного поиграть с приведенным выше сценарием. Ему не нравится вставляться в более крупный сценарий ниже.

 SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
BEGIN
    SET NOCOUNT ON;

Declare 
@StartDate Date,
@EndDate Date

SET @StartDate = '1/1/2016'
SET @EndDate = '6/1/2016'

WITH CNSL_Clients AS (
                SELECT PC_CNT.Clientkey, PC_Cnt.ProgramAdmitKey, PC_Cnt.OP__DOCID
                FROM FD__Primary_Client as PC_Cnt
                                INNER JOIN VW__Cnsl_Session_Count_IndvFamOnly as cnt
                                    ON PC_Cnt.Clientkey = CNT.Clientkey AND PC_Cnt.ProgramAdmitKey = CNT.ProgramAdmitKey
                WHERE ((pc_CNT.StartDate between @StartDate AND @EndDate) OR (pc_CNT.StartDate <= @StartDate AND pc_CNT.ENDDate >= @StartDate) OR (pc_CNT.StartDate <= @StartDate AND pc_CNT.ENDDate is null)) 
                AND CNT.SessionCount>=6
),

FIRST_BASIS AS (
            SELECT CB24_1.OP__DOCID, CB24_1.Date_Screening, CB24_1.ClientKey, CB24_1.ProgramAdmitKey, CB24_1.Composite_score, CB24_1.Depression_Results,CB24_1.Emotional_Results, CB24_1.Relationships_Results
            FROM FD__CNSLG_BASIS24 AS CB24_1
            WHERE (CB24_1.OP__DOCID =
                    (Select TOP(1) CB24_2.OP__DOCID
                    FROM FD__CNSLG_BASIS24 AS CB24_2
                                        Inner JOIN CNSL_Clients
                                            ON CB24_2.ClientKey = CNSL_Clients.ClientKey AND CB24_2.ProgramAdmitKey = CNSL_Clients.ProgramAdmitKey
                    WHERE (CB24_1.ClientKey = CB24_2.ClientKey) AND (CB24_1.ProgramAdmitKey = CB24_2.ProgramAdmitKey)
                    ORDER BY CB24_2.Date_Screening))
),

RECENT_BASIS AS (
            SELECT CB24_1.OP__DOCID, CB24_1.Date_Screening, CB24_1.ClientKey, CB24_1.ProgramAdmitKey, CB24_1.Composite_score, CB24_1.Depression_Results,CB24_1.Emotional_Results, CB24_1.Relationships_Results
            FROM FD__CNSLG_BASIS24 AS CB24_1
            WHERE (CB24_1.OP__DOCID =
                    (Select TOP(1) CB24_2.OP__DOCID
                    FROM FD__CNSLG_BASIS24 AS CB24_2
                                        Inner JOIN CNSL_Clients
                                            ON CB24_2.ClientKey = CNSL_Clients.ClientKey AND CB24_2.ProgramAdmitKey = CNSL_Clients.ProgramAdmitKey
                    WHERE (CB24_1.ClientKey = CB24_2.ClientKey) AND (CB24_1.ProgramAdmitKey = CB24_2.ProgramAdmitKey)
                    ORDER BY CB24_2.Date_Screening DESC))
)

SELECT F.OP__DOCID AS First_DOCID,R.OP__DOCID as Recent_DOCID,F.ClientKey, F.ProgramAdmitKey, F.Composite_Score AS FComposite_Score, R.Composite_Score as RComposite_Score, Composite_Change = R.Composite_Score - F.Composite_Score, F.Depression_Results AS FDepression_Results, R.Depression_Results AS RDepression_Resluts, Depression_Change = R.Depression_Results - F.Depression_Results, F.Emotional_Results AS FEmotional_Resluts, R.Emotional_Results AS REmotionall_Reslu, Emotional_Change = R.Emotional_Results - F.Emotional_Results, F.Relationships_Results AS FRelationships_Resluts, R.Relationships_Results AS RRelationships_Resluts, Relationship_Change = R.Relationships_Results - F.Relationships_Results
FROM First_basis AS F
        FULL Outer JOIN RECENT_BASIS AS R
            ON F.ClientKey = R.ClientKey AND F.ProgramAdmitKey = R.ProgramAdmitKey
ORDER BY F.ClientKey
END
GO
Метки: