SQL频率和Excel [英] SQL Frequency and Excel
问题描述
嗨因为您可能知道Excel中有一个频率函数,如:
= FREQUENCY(G3:G693,M683 :M693)
哪些会将列数据重组为新组并计算频率。
那么如何在存储过程中的SQL Server中做同样的事情???
非常感谢您的回复...
让我们在 MS Excel <中考虑以下示例/ i>(如何使用频率功能 [ ^ ]):
1.单元格中的数据A1:A10:
< pre lang =xml> A1:2
A2:5
A3:8
A4:11
A5:12
A6:19
A7 :21
A8:32
A9:45
A10:48
2.单元格间隔B1:B4:>
B1:10
B2:20
B3:30
B4:40
3.= FREQUENCY(A1:A10, B1:B4)
给出以下结果:
C1:3
C2:3
C3:1
C4:1
C5:2
SQL Server中没有这样的内置函数,所以你必须建立自己的查询。这是我为此目的的示例查询:
DECLARE @数据 表(值 INT );
INSERT INTO @ Data (值) VALUES ( 2 ),( 5 ),( 8 ),( 11 ),( 12 ),( 19 ),( 21 ),( 32 ),( 45 ),( 48 );
DECLARE @ Intervals TABLE (Value INT );
INSERT INTO @ Intervals (值) VALUES ( 0 ),( 10 ),( 20 ),( 30 ),( 40 跨度>);
; WITH Intervals1
AS ( SELECT 值,ROW_NUMBER() OVER ( ORDER BY 值) AS RowNumber
FROM @Intervals ),
Intervals2
AS ( SELECT t1.Value AS IntervalFrom,t2.Value AS IntervalTo
< span class =code-keyword> FROM Intervals1 AS t1
LEFT OUTER JOIN Intervals1 AS t2 ON t 2.RowNumber =(t1.RowNumber + 1 ))
SELECT IntervalTo AS Interval,COUNT(Value) AS 频率
FROM Intervals2, @ Data
WHERE (值> IntervalFrom) AND ((Value< IntervalTo) OR (IntervalTo IS NULL ))
GROUP BY IntervalFrom,IntervalTo
ORDER BY IntervalFrom;
我正在使用CTE在两个单独的列中获取间隔(IntervalFrom
,IntervalTo
)然后按这些间隔计算频率。
结果:
间隔频率
10 3
20 3
30 1
40 1
NULL 2
因为你没有提到使用哪个版本的sql server
这个用于sql2012
WITH CTE AS (
SELECT IVAL,LAG(IVAL, 1 , 0 ) OVER ( ORDER BY IVAL) AS PVAL
FROM INTERVALS
)
SELECT IVAL,COUNT(DVAL)
FROM DATA
LEFT < span class =code-keyword> JOIN CTE ON DVAL> PVAL AND DVAL< ival
GROUP BY IVAL
Hi So as you may know there is a Frequency function in Excel like:
=FREQUENCY(G3:G693,M683:M693)
Which will regroup column data into new group and count the frequency.
So how can I do the exact same thing in SQL Server in Stored Procedure???
Thank you so much for your reply...
Hi,
Let's consider the following example in MS Excel (How to Use the FREQUENCY Function[^]):
1. Data in cells A1:A10:
A1: 2 A2: 5 A3: 8 A4: 11 A5: 12 A6: 19 A7: 21 A8: 32 A9: 45 A10: 48
2. Intervals in cells B1:B4:
B1: 10 B2: 20 B3: 30 B4: 40
3.=FREQUENCY(A1:A10,B1:B4)
gives the following result:
C1: 3 C2: 3 C3: 1 C4: 1 C5: 2
There is no such built-in function in SQL Server, so you have to build your own query. Here's my sample query for this purpose:
DECLARE @Data TABLE (Value INT); INSERT INTO @Data (Value) VALUES (2), (5), (8), (11), (12), (19), (21), (32), (45), (48); DECLARE @Intervals TABLE (Value INT); INSERT INTO @Intervals (Value) VALUES (0), (10), (20), (30), (40); ;WITH Intervals1 AS (SELECT Value, ROW_NUMBER() OVER (ORDER BY Value) AS RowNumber FROM @Intervals), Intervals2 AS (SELECT t1.Value AS IntervalFrom, t2.Value AS IntervalTo FROM Intervals1 AS t1 LEFT OUTER JOIN Intervals1 AS t2 ON t2.RowNumber = (t1.RowNumber + 1)) SELECT IntervalTo AS Interval, COUNT(Value) AS Frequency FROM Intervals2, @Data WHERE (Value > IntervalFrom) AND ( (Value < IntervalTo) OR (IntervalTo IS NULL) ) GROUP BY IntervalFrom, IntervalTo ORDER BY IntervalFrom;
I'm using CTE to get intervals in two separate columns (IntervalFrom
,IntervalTo
) and then counting frequencies in these intervals.
Result:
Interval Frequency 10 3 20 3 30 1 40 1 NULL 2
since you are not mention which version of sql server used
this one is for for sql2012
WITH CTE AS ( SELECT IVAL,LAG(IVAL,1,0) OVER(ORDER BY IVAL) AS PVAL FROM INTERVALS ) SELECT IVAL, COUNT(DVAL) FROM DATA LEFT JOIN CTE ON DVAL > PVAL AND DVAL < ival GROUP BY IVAL
这篇关于SQL频率和Excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!