पिछले 24 घंटों के लिए गणना के साथ रिटर्न रिकॉर्ड - एसक्यूएल, एसक्यूएल-सर्वर, डेटाबेस, एसक्यूएल-सर्वर -2012

मैं पिछले 24 घंटों से रिकॉर्ड्स प्राप्त करने की कोशिश कर रहा हूं, जो SQL सर्वर में गणना के साथ घंटे के अनुसार समूहित है?

मेरे पास नमूना डेटा है जैसे:

ID   Dat
1    2015-01-19 10:29:00.000
2    2015-01-19 11:29:00.000
3    2015-01-19 11:29:00.000
4    2015-01-19 11:29:00.000
5    2015-01-19 12:29:00.000
6    2015-01-19 12:29:00.000
7    2015-01-19 12:29:00.000
8    2015-01-19 12:29:00.000
9    2015-01-17 13:29:00.000
10   2015-01-17 13:29:00.000
11   2015-01-17 13:29:00.000
12   2015-01-17 13:29:00.000
13   2015-01-17 13:29:00.000
14   2015-01-17 13:29:00.000
15   2015-01-17 14:29:00.000
17   2015-01-17 15:29:00.000
18   2015-01-17 15:29:00.000
19   2015-01-17 16:29:00.000
20   2015-01-17 16:29:00.000
21   2015-01-15 16:29:00.000
22   2015-01-15 17:29:00.000
23   2015-01-15 18:29:00.000
24   2015-01-15 18:29:00.000
25   2015-01-15 18:29:00.000
26   2015-01-15 18:29:00.000
27   2015-01-15 18:29:00.000
28   2015-01-15 18:29:00.000
29   2015-01-15 19:29:00.000
30   2015-01-10 20:29:00.000

अब मान लें कि वर्तमान दिनांक का समय है 2015-01-19 12:30:00.000, मेरा वांछित आउटपुट होगा:

Date                        Count
2015-01-19 12:00:00.000     4
2015-01-19 11:00:00.000     3
2015-01-19 10:00:00.000     1
2015-01-19 09:00:00.000     0
2015-01-19 08:00:00.000     0
2015-01-19 07:00:00.000     0
2015-01-19 06:00:00.000     0
2015-01-19 05:00:00.000     4
and so on...

तो गिनती रिकॉर्ड की संख्या पर आधारित होती है जो प्रत्येक घंटे में पड़ती है।

उत्तर:

उत्तर № 1 के लिए 1

इसे आज़माएं, यह डेटा के बिना घंटों की गणना भी करेगा:

DECLARE @t table(ID int, Date datetime)
INSERT @t values
(1,"2015-01-19 10:29:00.000"), (2,"2015-01-19 11:29:00.000"),
(3,"2015-01-19 11:29:00.000"), (4,"2015-01-19 11:29:00.000"),
(5,"2015-01-19 12:29:00.000"), (6,"2015-01-19 12:29:00.000"),
(7,"2015-01-19 12:29:00.000"), (8,"2015-01-19 12:29:00.000"),
(9,"2015-01-17 13:29:00.000"), (10,"2015-01-17 13:29:00.000"),
(11,"2015-01-17 13:29:00.000"),(12,"2015-01-17 13:29:00.000"),
(13,"2015-01-17 13:29:00.000"),(14,"2015-01-17 13:29:00.000"),
(15,"2015-01-17 14:29:00.000"),(17,"2015-01-17 15:29:00.000"),
(18,"2015-01-17 15:29:00.000"),(19,"2015-01-17 16:29:00.000"),
(20,"2015-01-17 16:29:00.000"),(21,"2015-01-15 16:29:00.000"),
(22,"2015-01-15 17:29:00.000"),(23,"2015-01-15 18:29:00.000"),
(24,"2015-01-15 18:29:00.000"),(25,"2015-01-15 18:29:00.000"),
(26,"2015-01-15 18:29:00.000"),(27,"2015-01-15 18:29:00.000"),
(28,"2015-01-15 18:29:00.000"),(29,"2015-01-15 19:29:00.000"),
(30,"2015-01-10 20:29:00.000")

DECLARE @yourdate datetime = "2015-01-19T12:30:00.000"

;WITH CTE AS
(
SELECT dateadd(hh, datediff(hh, 0, @yourdate), 0) Date
UNION ALL
SELECT dateadd(hh, -1, Date)
FROM CTE
WHERE Date + 1 > @yourdate
)
SELECT CTE.Date, count(t.id) count
FROM CTE
LEFT JOIN @t t
ON CTE.Date <= t.Date
and dateadd(hh, 1, CTE.Date) > t.Date
GROUP BY CTE.Date
ORDER BY CTE.Date DESC

परिणाम:

Date                    Count
2015-01-19 12:00:00.000 4
2015-01-19 11:00:00.000 3
2015-01-19 10:00:00.000 1
2015-01-19 09:00:00.000 0
2015-01-19 08:00:00.000 0
.....

जवाब के लिए 3 № 2

आप ऐसा कर सकते हैं अपने मूल्यों को निकटतम घंटे में गोल करें और फिर बस ग्रुप और COUNT:

एसक्यूएल फिल्ड डेमो

एमएस एसक्यूएल सर्वर स्कीमा सेटअप:

CREATE TABLE DateTable
([ID] int, [Date] datetime)
;

INSERT INTO DateTable
([ID], [Date])
VALUES
(1, "2015-01-19 10:29:00"),
(2, "2015-01-19 11:29:00"),
(3, "2015-01-19 11:29:00"),
(4, "2015-01-19 11:29:00"),
(5, "2015-01-19 12:29:00"),
(6, "2015-01-19 12:29:00"),
(7, "2015-01-19 12:29:00"),
(8, "2015-01-19 12:29:00"),
(9, "2015-01-17 13:29:00"),
(10, "2015-01-17 13:29:00"),
(11, "2015-01-17 13:29:00"),
(12, "2015-01-17 13:29:00"),
(13, "2015-01-17 13:29:00"),
(14, "2015-01-17 13:29:00"),
(15, "2015-01-17 14:29:00"),
(17, "2015-01-17 15:29:00"),
(18, "2015-01-17 15:29:00"),
(19, "2015-01-17 16:29:00"),
(20, "2015-01-17 16:29:00"),
(21, "2015-01-15 16:29:00"),
(22, "2015-01-15 17:29:00"),
(23, "2015-01-15 18:29:00"),
(24, "2015-01-15 18:29:00"),
(25, "2015-01-15 18:29:00"),
(26, "2015-01-15 18:29:00"),
(27, "2015-01-15 18:29:00"),
(28, "2015-01-15 18:29:00"),
(29, "2015-01-15 19:29:00"),
(30, "2015-01-10 20:29:00")
;

समेकित डेटा वापस करने के लिए क्वेरी:

SELECT  DATEADD(HOUR, DATEDIFF(HOUR, 0, [DATE]), 0) As [DateValue],
COUNT(1) AS 1
FROM    DateTable
WHERE [DATE] >= DATEADD(day, -1, GETDATE())
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, [DATE]), 0)
ORDER BY 1

परिणाम:

|                      DATEVALUE | COUNT |
|--------------------------------|-------|
| January, 19 2015 10:00:00+0000 |     1 |
| January, 19 2015 11:00:00+0000 |     3 |
| January, 19 2015 12:00:00+0000 |     4 |

यह उपयोग कर रहा है GETDATE() वर्तमान दिनांक समय मूल्य वापस करने और बिंदु से पिछले 24 घंटे लेने के लिए। ऊपर दी गई क्वेरी के लिए नीचे से दिए गए मान का उपयोग करता है WHERE खंड:

SELECT DATEADD(day, -1, GETDATE())

आप फ़िल्टर मान को प्रतिस्थापित कर सकते हैं WHERE यदि आवश्यक हो तो एक चर के साथ खंड।


उत्तर № 3 के लिए 1
create table #temptable
(
ID int ,
Date datetime
)
go
insert into #temptable (id, Date)
select 1 , "2014-01-19 10:29:00.000" union all
select 2 , "2014-01-19 11:29:00.000" union all
select 3 , "2014-01-19 11:29:00.000" union all
select 4 , "2014-01-19 11:29:00.000" union all
select 5 , "2014-01-19 09:29:00.000" union all
select 6 , "2014-01-19 08:29:00.000" union all
select 7 , "2014-01-19 03:29:00.000"

SELECT CAST(Date as date) AS ForDate,
DATEPART(hour,Date) AS OnHour,
COUNT(*) AS Totals
FROM #temptable
GROUP BY CAST(Date as date),
DATEPART(hour,Date)

एसक्यूएल सर्वर समूह प्रति दिन डेटटाइम की गणना से?


जवाब के लिए 0 № 4

It may helps you

declare  @t table(t datetime)
insert into @t values(getdate()),(getdate())

SELECT cast(cast(getdate() as date) as datetime)+cast(datepart(hour,getdate()) as float)/24, count(*)
from @t
group by cast(cast(getdate() as date) as datetime)+cast(datepart(hour,getdate()) as float)/24

जवाब के लिए 0 № 5

इसे इस्तेमाल करे

एसक्यूएल सर्वर के लिए

select CAST( MyDateColumn as DATE) ,DATEPART(HOUR, MyDateColumn),COUNT(*)
from MyTable
GROUP BY CAST( MyDateColumn as DATE) ,DATEPART(HOUR, MyDateColumn)

संबंधित सवाल
सबसे लोकप्रिय