Qanday SQL ma'lumotlar bilan birikma ma'lumotlarini yaratishim mumkin?

Menda bu stol DDL bor:

CREATE TABLE [dbo].[Audit] (
    [AuditId] INT              IDENTITY (1, 1) NOT NULL,
    [Entity]  INT              NOT NULL,
    [Action]  INT              NOT NULL,
    [Id]      UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED ([AuditId] ASC)
);

Update - I added the missing SQL here. Sorry about that

Nima qilsam, bu SQL yordamida har kuni qanday tadbir borligini ko'rsata oladigan SQL hisoboti yaratildi:

select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
       [Col2] = convert(varchar, count(*))
from Audit a
group by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101)
order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

Chiqish shunday ko'rinadi:

col1        Col2
03/05/2017  1
03/04/2017  20
03/03/2017  10
03/02/2017  5
03/01/2017  10

Endi men nima qilishim kerakligini hisoblash emas, balki kümülatif qiymatni ko'rsatadigan SQL tanlovini yaratishdir. Shunday qilib, SQL-ni ko'rsatadigan hisobot tuzish kerak

col1        Col2
03/05/2017  46
03/04/2017  45
03/03/2017  25
03/02/2017  15
03/01/2017  10

Ushbu turdagi hisobotni yaratish uchun SQLni qanday o'zgartirishim mumkinligi haqida har qanday fikr bormi?

Esda tutingki, men aslida bitta buyruq echimini izlayapman, chunki u .net tuzilmasidan amalga oshiriladi va agar bir nechta buyruqlar mavjud bo'lsa, men buni qandaydir usulda saqlashga harakat qilish kerak deb o'ylayman.

6
Bu umumiy ish. Bunga yechim foydalanadigan SQL server versiyasiga bog'liq. Ushbu saytda jami jami o'nlab va o'nlab misollar mavjud.
qo'shib qo'ydi muallif Sean Lange, manba
Chiqish tanlangan * dan yoki ?? Birinchi qatordan boshlanadigan jami summadan 46 ga qanday erishdingiz?
qo'shib qo'ydi muallif scsimon, manba
Salom @ Anne, biz sizning to'g'ri natijalarni olish uchun sizning SQL-ni o'zgartirishga yordam berishni xohlaymiz. Afsuski, siz SQL kodingizni taqdim etmadingiz, shuning uchun qanday o'zgarishlarni taklif qilishni bilmaymiz.
qo'shib qo'ydi muallif KindaTechy, manba
Savolim uchun nima uchun 2 ovoz olganman? Har bir insonning fikri bormi?
qo'shib qo'ydi muallif user1464139, manba
@EdwardRusu Hisobotni yaratishda ishlatiladigan SQL-ni o'z ichiga olmaganim uchun juda achinaman. Men hozir qo'shaman.
qo'shib qo'ydi muallif user1464139, manba

6 javoblar

Siz Oyna vazifalari Sum() Over dan foydalanishingiz mumkin

Select col1 = convert(varchar(10),cast(dateadd(HOUR, 8, a.date) as date),101)
      ,col2 = sum(count(*))  over (Order by cast(dateadd(HOUR, 8, a.date) as date))
 From  Audit a
 Group by cast(dateadd(HOUR, 8, a.date) as date)
 Order By cast(dateadd(HOUR, 8, a.date) as date) Desc

Qaytishlar

col1        Col2
03/05/2017  46
03/04/2017  45
03/03/2017  25
03/02/2017  15
03/01/2017  10
5
qo'shib qo'ydi
@Anne Yangilangan javobni ko'ring
qo'shib qo'ydi muallif John Cappelletti, manba
@JohnCappelletti Qisqasi, qisqartma bilan ham bu erda eng yaxshi javob bo'ldi.
qo'shib qo'ydi muallif SqlZim, manba
@JohnCappelletti Men tushunaman, sen stenoktaga ishlatilgansiz va u yaxshi ishlaydi. Buni uzoq vaqt davomida ishlatardim. Stenga qarshi argument faqat ravshanlik uchundir, chunki kamroq yoritilgan qisqartmalarning ba'zilari juda kam hollarda bo'ladi va ba'zilari nima qilish kerakligini tushunish uchun faqatgina yoritmoq jadvaliga murojaat qilish kerak. Albatta, soat uchun hh deb o'ylayman. Men hozirgi kunga borishni xohlar edim, lekin oldingi sharhni yozmasdan oldin allaqachon yuqoriga ko'targan edim.
qo'shib qo'ydi muallif SqlZim, manba
Yana bir yomon odat Itzik Ben-Gan : "iloji boricha ROWS formasini yopishtiring va ramkaga tortmasdan buyurtma berishni ko'rsatmaslikga harakat qiling". Siz undan SUM (...) OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) matnini yozib qo'ymasligingiz kerak va uni RANGE -ga sukut qilib qo'yishingiz kerak. Ushbu maxsus vaziyatda har ikkala variant ham har doim bir xil natijalarni beradi, lekin ROWS biroz samarali bo'lishi mumkin. Umuman olganda, natijalar har xil bo'lishi mumkin, shuning uchun har doim ROWS yoki RANGE so'zlarini aytish kerak.
qo'shib qo'ydi muallif Vladimir Baranov, manba
Kechirasiz, jami hisobot tuzish uchun o'zgartirishni xohlagan SQL tanlovim borligini eslatib qo'yganimdan afsusdaman. Sizning taklifingiz yaxshi, lekin bu savolga hozir qo'shilgan dastlabki tanlovga bog'liq bo'lishi mumkin. Rahmat
qo'shib qo'ydi muallif user1464139, manba

Bir buyruqdagi summani olish uchun "bilan" iborasidan foydalaning. Shunga o'xshash:

WITH TBL ([col1], [col2])  
AS  
(  
SELECT [col1] = CONVERT(DATE, DATEADD(HOUR, 8, a.date)),
       [Col2] = COUNT(*)
FROM Audit a
GROUP BY CONVERT(DATE, DATEADD(HOUR, 8, a.date))
)  
SELECT  [col1] = CONVERT(VARCHAR(10), t1.[col1], 101), 
        [col2] = CONVERT(VARCHAR, SUM(t2.[Col2]))
FROM TBL t1 INNER JOIN TBL t2 on t1.col1 >= t2.col1
GROUP BY t1.col1
ORDER BY t1.col1 DESC

Omad!

4
qo'shib qo'ydi
Bu jamuljamlikni olish uchun sizning javobingiz. Yodda tutish kerak bo'lgan ikkita narsa. 1- BIR so'zdan oldin; 2- SQL serverda standart max ruyxlar mavjud (100 ishonaman); buni MAXRECURSION() iborasi bilan kengaytira olasiz.
qo'shib qo'ydi muallif Jim, manba

Agar Auditorlik jadvalining ma'lumotlar namunasini taqdim etsangiz, sizning natijalaringiz ma'lumotlarini emas, balki optmize va aniq so'rovni qabul qilishingiz mumkin.

Sql-2012dan foydalanish,

DECLARE @Audit TABLE (
    [col1]  datetime  NOT NULL
    ,[col2]  INT  NOT NULL
)

insert into @audit(col1,Col2)
VALUES
('03/05/2017',  1  )
,('03/04/2017',  20 )
,('03/03/2017',  10 )
,('03/02/2017',  5  )
,('03/01/2017',  10 )

;WITH CTE as
(
select col1 ,sum(col2) over(ORDER BY col1 desc  RANGE BETWEEN 
CURRENT ROW AND UNBOUNDED FOLLOWING) AS col2
from @Audit
)
SELECT * FROM cte   
order by col1 desc
2
qo'shib qo'ydi
@ Ota-onalar, real ma'lumotlarga ko'ra, oraliq oralig'i o'rniga satrlarni ishlatishingiz mumkin.
qo'shib qo'ydi muallif KumarHarsh, manba

So'rovni faqat quyida ko'rsatilgan so'rovlar bilan o'zgartirish (bu so'rov faqat SQL SERVER 2012 dan foydalanilganda ishlaydi)

Buni ko'ring:

    DECLARE Audit TABLE (date datetime,
        [col1]  int  NOT NULL
    )

    insert into Audit(date,Col1)
    VALUES
    ('03/05/2017',  1  )
    ,('03/04/2017',  20 )
    ,('03/03/2017',  10 )
    ,('03/02/2017',  5  )
    ,('03/01/2017',  10 )


    select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
           [Col2] = SUM(col1)  over (Order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101))
    from Audit a
    order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc
1
qo'shib qo'ydi

Quyidagi kabi biror narsani sinashingiz mumkin.

IF OBJECT_ID('tempdb..#CumulativeSum') IS NOT NULL
    DROP TABLE #CumulativeSum

SELECT t1.col1, t1.col2 AS col2Actual, SUM(t2.col2) AS col2
INTO #CumulativeSum
FROM Audit t1
INNER JOIN Audit t2 on t1.col1 >= t2.col1
GROUP BY t1.col1, t1.col2
ORDER BY t1.col1

SELECT col1, col2 FROM #CumulativeSum ORDER BY col2 DESC

Javoblar FiRiTiTi taklif qilinganidek, quyidagi javoblardan ko'chirildi.

Kümülatif pulni qanday olish mumkin

Tartibga solish - bitta buyruq bo'yicha so'rov qilish uchun.

SELECT col1, col2 FROM(
    SELECT TOP 100 PERCENT t1.col1, t1.col2 AS col2Actual, SUM(t2.col2) AS col2
    FROM Audit t1
    INNER JOIN Audit t2 on t1.col1 >= t2.col1
    GROUP BY t1.col1, t1.col2
    ORDER BY t1.col1)x
ORDER BY col2 DESC

Umid qilamanki, bu sizning aniq talablarga erishishga yordam beradi.

1
qo'shib qo'ydi
saqlangan skript protsedurasini yarating ... agar uni bitta satr deb atashni xohlasangiz.
qo'shib qo'ydi muallif maSTAShuFu, manba
Buni faqatgina bitta buyruq bilan olishim mumkinmi? Buning sababi exec buyrug'idan foydalanib .net-ni bajarishdir. Amaliyotingiz bilan birovni o'zida saqlab yurish tartibiga qo'yish kerak. Rahmat
qo'shib qo'ydi muallif user1464139, manba
Anne so'rovni faqat bitta bayonotda bo'lishini talab qilib, javob berildi.
qo'shib qo'ydi muallif Naim Halai, manba
Muhim kod kiritilgan deb javob berildi.
qo'shib qo'ydi muallif Naim Halai, manba

Quyidagi so'rovni sinab ko'ring

select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
       [Col2] = (select count(*) from Audit b where b.date<= DATEADD(HOUR, 8, a.date))
from Audit a
group by DATEADD(HOUR, 8, a.date)
order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc
1
qo'shib qo'ydi