J'ai un tableau comme suit
Domain Baseline PlanningPeriod Actual CumActual ---------------------------------------------------------------- Dom 1 Jan 18 1/1/18 10 10 Dom 1 Jan 18 1/2/18 20 30 Dom 1 Jan 18 1/3/18 25 55 Dom 1 Jan 18 1/4/18 24 79 Dom 1 Feb 18 1/2/18 60 **79** Dom 1 Feb 18 1/3/18 20 80 Dom 1 Feb 18 1/5/18 40 120 Dom 1 Feb 18 1/12/18 70 190 Dom 1 Mar 18 1/3/18 11 **190** Dom 1 Mar 18 1/4/18 12 23 Dom 1 Mar 18 1/12/18 18 31 Dom 1 Apr 18 1/4/18 40 **31** Dom 1 Apr 18 1/5/18 25 65 Dom 1 Apr 18 1/6/18 15 80 --------------------------------------------------------
Veuillez noter qu'il existe également différents domaines
J'essaie donc d'obtenir une colonne supplémentaire comulativeactual groupée par domaine, référence, planningperiod.
Pour le but, j'ai fait la requête ci-dessous
Domain Baseline PlanningPeriod Actual CumActual ---------------------------------------------------------------- Dom 1 Jan 18 1/1/18 10 10 Dom 1 Jan 18 1/2/18 20 30 Dom 1 Jan 18 1/3/18 25 55 Dom 1 Jan 18 1/4/18 24 79 Dom 1 Feb 18 1/2/18 60 60 Dom 1 Feb 18 1/3/18 20 80 Dom 1 Feb 18 1/5/18 40 120 Dom 1 Feb 18 1/12/18 70 190 Dom 1 Mar 18 1/3/18 11 11 Dom 1 Mar 18 1/4/18 12 23 Dom 1 Mar 18 1/12/18 18 31 Dom 1 Apr 18 1/4/18 40 40 Dom 1 Apr 18 1/5/18 25 65 Dom 1 Apr 18 1/6/18 15 80 --------------------------------------------------------
Cela a fonctionné comme prévu et a donné le résultat suivant
select domain,baseline,planningperiod, actual,sum(actual) over(partition by domain, baseline order by domain,baseline,planningperiod) as cumilativeactual
Mais mon exigence est un peu sauvage. J'ai besoin du dernier élément de chaque groupe comme premier élément du groupe suivant. Mais les autres valeurs du groupe exactement comme ci-dessus. Alors je ne savais vraiment pas comment y parvenir. Je ne préfère pas le curseur car il y a environ 20k enregistrements.
Les moyens ci-dessus devraient fonctionner comme suit.
Domain Baseline PlanningPeriod Actual ------------------------------------------------- Dom 1 Jan 18 1/1/18 10 Dom 1 Jan 18 1/2/18 20 Dom 1 Jan 18 1/3/18 25 Dom 1 Jan 18 1/4/18 24 Dom 1 Feb 18 1/2/18 60 Dom 1 Feb 18 1/3/18 20 Dom 1 Feb 18 1/5/18 40 Dom 1 Feb 18 1/12/18 70 Dom 1 Mar 18 1/3/18 11 Dom 1 Mar 18 1/4/18 12 Dom 1 Mar 18 1/12/18 18 Dom 1 Apr 18 1/4/18 40 Dom 1 Apr 18 1/5/18 25 Dom 1 Apr 18 1/6/18 15 -------------------------------------------------
5 Réponses :
Vous semblez vouloir une somme cumulative avec une logique supplémentaire:
select domain, baseline, planningperiod, actual,
(case when seqnum = 1
then lag(periodtotal) over (partition by domain, baseline order by domain, baseline, planningperiod)
else cumulativeactual
end) as cum_actual
from (select domain, baseline, planningperiod, actual,
sum(actual) over (partition by domain, baseline order by domain, baseline, planningperiod) as cumulativeactual,
sum(actual) over (partition by domain, baseline) as periodtotal,
row_number() over (partition by domain, baseline order by domain, baseline, planningperiod) a seqnum
from t
) t;
Quelques fonctions de fenêtre peuvent vous aider à remplacer la somme glissante d'origine par la précédente sur le premier pour chaque groupe.
Exemple de données (mêmes groupes et valeurs que votre exemple): p>
Code Actual DenseRank RowNumberByCodeAsc RowNumberByCodeDesc CumulativeGeneral PreviousFinishingCumulativeGeneral CorrectedCumulative A 10 1 1 4 10 NULL 10 A 20 1 2 3 30 NULL 30 A 25 1 3 2 55 NULL 55 A 24 1 4 1 79 NULL 79 B 60 2 1 4 60 79 79 B 20 2 2 3 80 79 80 B 40 2 3 2 120 79 120 B 70 2 4 1 190 79 190 C 11 3 1 3 11 190 190 C 12 3 2 2 23 190 23 C 18 3 3 1 41 190 41 D 40 4 1 3 40 41 41 D 25 4 2 2 65 41 65 D 15 4 3 1 80 41 80
Requête :
;WITH WindowFunctions AS
(
SELECT
A.Code,
A.Actual,
DenseRank = DENSE_RANK() OVER (ORDER BY A.Code),
RowNumberByCodeDesc = ROW_NUMBER() OVER (PARTITION BY A.Code ORDER BY A.Ordering DESC),
RowNumberByCodeAsc = ROW_NUMBER() OVER (PARTITION BY A.Code ORDER BY A.Ordering ASC),
CumulativeGeneral = SUM(A.Actual) OVER (PARTITION BY A.Code ORDER BY A.Ordering ASC)
FROM
#Amounts AS A
)
SELECT
G.Code,
G.Actual,
G.DenseRank,
G.RowNumberByCodeAsc,
G.RowNumberByCodeDesc,
G.CumulativeGeneral,
PreviousFinishingCumulativeGeneral = P.CumulativeGeneral,
CorrectedCumulative = CASE
WHEN G.RowNumberByCodeAsc = 1 AND P.CumulativeGeneral IS NOT NULL THEN P.CumulativeGeneral
ELSE G.CumulativeGeneral END
FROM
WindowFunctions AS G
LEFT JOIN WindowFunctions AS P ON
G.DenseRank - 1 = P.DenseRank AND
P.RowNumberByCodeDesc = 1
Résultats :
IF OBJECT_ID('tempdb..#Amounts') IS NOT NULL
DROP TABLE #Amounts
CREATE TABLE #Amounts (
Code VARCHAR(10),
Actual INT,
Ordering INT IDENTITY)
INSERT INTO #Amounts (
Code,
Actual)
VALUES
('A', 10),
('A', 20),
('A', 25),
('A', 24),
('B', 60),
('B', 20),
('B', 40),
('B', 70),
('C', 11),
('C', 12),
('C', 18),
('D', 40),
('D', 25),
('D', 15)
Vous pouvez utiliser un CTE et LAG() comme
with cte as
(
select domain,
baseline,
planningperiod,
actual,
sum(actual) over(partition
by domain, baseline order by domain,baseline,planningperiod) as cumilativeactual
from t
)
select domain,
baseline,
planningperiod,
actual,
case when actual = cumilativeactual then
lag(cumilativeactual, 1, 0) over(order by domain,baseline,planningperiod)
else
cumilativeactual end as cumilativeactual
from cte
C'est la solution la plus simple.
Merci beaucoup pour la solution ... Cela a très bien fonctionné. Je m'attends à un petit bogue puisque nous vérifions actual = cumulativeactual. Au lieu de cela, j'y ai apporté un petit changement et l'ai affiché comme une réponse qui pourrait être utile pour quelques
Je suppose que comme la sortie est triée par cumilativeactual , vous pouvez simplement utiliser un cas lorsque first_value / min_value du groupe puis (sélectionnez max (CumActual) dans prev_group (en utilisant row_number () - 1 )
Merci @Sami pour la belle solution. Mais j'y fais un petit ajustement qui pourrait le rendre un peu plus précis je suppose.
Ici, ma requête est
with cte as
(
select domain,
baseline,
planningperiod,
actual,
sum(actual) over(partition
by domain, baseline order by domain,baseline,planningperiod) as cumilativeactual,DENSE_RANK() over(order by domain,baseline) as DRank
from t
)
select domain,
baseline,
planningperiod,
actual,
case when Drank> LAG(DRank,1,0) over(order by domain,baseline,planningperiod) then
lag(cumilativeactual, 1, 0) over(order by domain,baseline,planningperiod)
else
cumilativeactual end as cumilativeactual
from cte