如何在SQL Server中找到平均值？ [英] How to find average in SQL Server?

问题描述

大家好，



我必须在我的商店里找到平均总访问量。例如，如果我在过去30天内生成查询，则报告应该类似于

Hello All,

I have to find the average of total visitors in my shop day wise. For eg, if I'm generating query for last 30 days, the report should be like

Date      Customers         Average

1 Oct           100          100
2 Oct           50          (100+50)/2
3 Oct          100          (100+50+100)/3
4 Oct          100           (100+50+100+100)/4 etc

有谁能帮我写这个sql查询？请查找查询以查找客户数量。我想在其中添加平均列。

Can anyone help me to write sql query for this? Please find the query to find customer count. I want to add average column in it.

select branch.br_name ,tran_date as entry_date ,count(distinct slip_no) as no_of_cust
from rem_upload , branch
where rem_upload.location_id = branch.br_code
and ( ( rem_upload.location_id like '002' )
AND  ( rem_upload.tran_date ='01/01/2015' )
AND  ( rem_upload.tran_date = '12/10/2015' ) )
group by branch.br_name  ,tran_date

推荐答案

如果你有SQL Server 2012或更高版本，你可以使用窗口函数：

If you have SQL server 2012 or higher you can use windowing functions:

select [date], [customers], AVG([customers]) OVER(ORDER BY [date]) as [average] from t

除了 Tomas Takac [ ^ ]，如果你使用SQL Server到2012年，你可以用这个来实现同样的目的：

In addition to solution 2 by Tomas Takac[^], if you use SQL Server down to 2012, you can achieve the same using this:

DECLARE @tmp TABLE(VisitDate DATE, CustomersCount INT)

INSERT INTO @tmp (VisitDate, CustomersCount)
VALUES('2015-10-01', 100),
('2015-10-02', 50),
('2015-10-03', 100),
('2015-10-04', 100)

SELECT t1.VisitDate, SUM(t2.CustomersCount) AS RunningSum, COUNT(t2.VisitDate) AS CountOfDays, SUM(t2.CustomersCount)/COUNT(t2.VisitDate) AS [Average]
FROM @tmp AS t1 INNER JOIN @tmp AS t2 ON t1.VisitDate>=t2.VisitDate
GROUP BY t1.VisitDate

VisitDate	RunningSum	CountOfDays	Average
2015-10-01	100			1			100
2015-10-02	150			2			75
2015-10-03	250			3			83
2015-10-04	350			4			87

您好，



查看以下示例，希望这会对你有所帮助......

Hi,

Check below example, hope this will help you...

--My Table based on your requirement
Day1                    | count1
------------------------------------------
2015-10-01 12:38:19.487 | 100
2015-10-02 12:38:26.253 | 50
2015-10-03 12:38:33.540 | 150
2015-10-04 12:38:43.530 | 100

--Query to fetch Total, Avg Counts

SELECT DAY(day1) as DayCount, 
count1 as NoOfCustomer,
Count1 + ISNULL((SELECt SUM(count1) from Stats_Test B where DAY(B.Day1) < DAY(A.Day1)),0) as TotalNoOfCustomer,
(Count1 + ISNULL((SELECt SuM(count1) from Stats_Test B where DAY(B.Day1) < DAY(A.Day1) ),0))/DAY(day1) as AvGNoOfCustomer
from 
Stats_Test A

--And OutPut is 

DayCount	| NoOfCustomer	| TotalNoOfCustomer	| AvGNoOfCustomer
--------------------------------------------------------------------------
1	        | 100	        | 100	                | 100
2	        | 50	        | 150	                | 75
3	        | 150	        | 300	                | 100
4	        | 100	        | 400	                | 100

您可以自定义以上查询以符合您的要求。





检查您的查询...希望它应该工作，因为我没有运行此。

You can customize above query to fit in your requirement.


Check Your Query...hope it should work as i have not run this.

Select X.br_name,
X.entry_date,
X.no_of_cust,
(X.no_of_cust+ ISNULL((SELECt SuM(X.no_of_cust) from X B where DAY(B.entry_date) < DAY(X.entry_date) ),0))/DAY(X.entry_date) as AvgCount
(
select branch.br_name,
tran_date as entry_date,
count(distinct slip_no) as no_of_cust
from rem_upload,branch
where rem_upload.location_id = branch.br_code
and ( ( rem_upload.location_id like '002' )
AND  ( rem_upload.tran_date ='01/01/2015' )
AND  ( rem_upload.tran_date = '12/10/2015' ) )
group by branch.br_name,tran_date
) X

干杯

Cheers

这篇关于如何在SQL Server中找到平均值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！