在SQL 2005中复制Excel模式功能 [英] Replicate Excel Mode function in SQL 2005

问题描述

此SQL列出了代码，句点和输入文件的每个组合的所有模态值。

;  with  AmountCounts  as  
（ SELECT  aa.code，aa .Period，aa.inputfile，aa.amount，COUNT（*） AS 出现
  FROM  tempPivotTable aa 
  GROUP   BY  aa.code，aa.Period，aa.inputfile，aa。金额），
 MaxOccurences  as  
（ select  t.code，'  O'  as  vendor，t.Period，Filedate ，t.inputfile，YearlyOrQuarterly，sortablePeriod，NumericFiledate，max（出现次数） as  MaxAmount 
 来自 TempPivotTable t 
 内部 加入 AmountCounts A 
  on  t.code = a。 code 和 t.period = a.period 和 t.inputfile = a.inputfile 
 < span class =code-keyword> group   by  t.code，t.Period，Filedate，t.inputfile，YearlyOrQuarterly，sortablePeriod，NumericFiledate）
 选择 * 来自 AmountCounts A 
 内部  join  MaxOccurences M 
  on  A.code = M.code 和 A.period = M.period 和 A.inputfile = M.Inputfile 
 其中出现次数= maxamount 
 订单 按 A.代码，A.period，A.inputfile 

我需要它表现得更像Excel。



对于这一系列的数字，Excel返回8的模式。这可能是因为8是第一个出现的模态数字。您提供的SQL也需要这样做。

 6 
 7 
 8 
 3 
 3 
 8 
 0 
 2 
 2 

如果有的话没有模式，例如所有数字都是唯一的，它应该返回NA。



我还需要使用union语句运行代码，其中包含我用于中位数的代码

 选择代码，句号，Filedate，inputfile，YearlyOrQuarterly，sortablePeriod，NumericFiledate，
 Count（*） As  MonthCounter  From  TempPivotTable 
  Group  按 Code，period，filedate，inputfile，
 YearlyOrQuarterly，sortablePeriod，NumericFiledate 
） As  MonthCount 
 内部 加入（
  select  amount，Code，period，inputfile，Row_Number（） Over （ Partition  按代码，句点，inputfile 订单 按金额）作为 RowNumber 
 来自 TempPivotTable 
 ） As  RowCounts 
  On  MonthCount.Code = RowCounts.Code 
 和 MonthCount.period = RowCounts.period 
 和 MonthCount.inputfile = RowCounts.inputfile 
 < span class =code-keyword>其中  2  * RowNumber  -  MonthCounter 介于  0  和  2  
 组 按 MonthCount.Code，MonthCount.period，Filedate，MonthCount.inputfile，YearlyOrQuarterly，sortablePeriod，NumericFiledate 
 订单 按 MonthCount.Code，MonthCount.period，Filedate ，MonthCount.inputfile，YearlyOrQuarterly，sortablePeriod，NumericFiledate 

解决方案

我认为这样更好。

我认为 它。 
 
 
 
 选择代码，inputfile，period，Amount，AmountCount，Ranking 进入 #tmp1 来自 
（
 选择代码， inputfile，period，Amount，count（*） as  Amountcount，
 Ranking = dense_Rank（） over （分区 按代码，句点，输入文件命令 按 count（*） desc ）
 来自 TempPivotTable 
   -   其中code ='USCPIAP'和period ='2007'和inputfile ='C：\FalconIngest \Input \Final US Long Oct06 input.xls' 
  group   by 代码，inputfile，句点，金额
） as  A 
 
  select  code，inputfile，period，金额，FirstVendor 进入 #tmp2 来自（
 选择 t.code，t.inputfile，t.period，t.amount，min（vendor） as  FirstVendor  from  tempPivotTable t 
  inner   join  #tmp1 r 
 < span class =code-keyword> on  t.code = r.code 
 和 t.inputfile = r.inputfile 
 和 t.period = r.period 
 和 t.amount = r.amount 
 其中排名= 1 
  group   by  t.code，t.inputfile，t.period，t.amount 
） as  A 
 
 
 选择代码，inputfile，句点，FirstVendor 进入 #tmp3 来自（
 选择代码，inputfile，句点，分钟（Firstvendor） as  FirstVendor 来自＃tmp2 
  group   by 代码，输入文件，句号） as  A 
 
   -   其中没有模式设置第一个数字= -999999，所以程序可以转换为NA。 
 
 选择代码，供应商，期间，Filedate，inputfile，YearlyOrQuarterly，sortablePeriod，numericFiledate，amount，AmountCount  into  #tmp4 来自（
 选择 tp.code，'  O'  as  vendor，tp.period，tp.Filedate，tp.inputfile，tp.YearlyOrQuarterly，tp.sortablePeriod，tp.numericFiledate，t2.amount ，t1.AmountCount 
 来自 tempPivotTable tp 
  inner   join  #tmp1 t1 
  on  tp.code = t1.code 和 tp.inputfile = t1.inputfile 和 tp.period = t1.period 
  inner   join  #tmp2 t2 
  on  t1.code = t2.code 和 t1.inputfile = t2.inputfile 和 t1.period = t2.period 和 t1.amount = t2.amount 
  inner   join  #tmp3 t3 
   t2.firstvendor = t3.firstvend或
 和 tp.vendor = t3.firstvendor 
 和 tp.code = t3.code 
 和 tp.period = t3.period 
 和 tp。 inputfile = t3.inputfile 
） as  A 
 
 
  update  #tmp4  set 金额= -999999 其中金额= 1 
 
 
 
  drop   table  #tmp1 
  drop   table  #tmp2 
  drop   table  #tmp3 
  drop   table  #tmp4 

This SQL lists all modal values for each combination of code,period and inputfile.

;with AmountCounts as
(SELECT aa.code,aa.Period,aa.inputfile,aa.amount,COUNT(*) AS occurrences
FROM tempPivotTable aa
GROUP BY aa.code,aa.Period,aa.inputfile,aa.amount),
MaxOccurences as
(select t.code,'O' as vendor,t.Period,Filedate,t.inputfile,YearlyOrQuarterly,sortablePeriod,NumericFiledate,max(occurrences) as MaxAmount
from TempPivotTable t
inner join AmountCounts A
on t.code=a.code and t.period=a.period and t.inputfile=a.inputfile
group by t.code,t.Period,Filedate,t.inputfile,YearlyOrQuarterly,sortablePeriod,NumericFiledate)
select * from AmountCounts A
inner join MaxOccurences M
on A.code=M.code and A.period=M.period and A.inputfile=M.Inputfile
where occurrences=maxamount
order by  A.code,A.period,A.inputfile

I need it to behave more like Excel.

For this series of numbers Excel returns a mode of 8. This is probably because 8 is the first modal number to appear. The SQL you give needs to do the same.

6
7
8
3
3
8
0
2
2

If there is no mode eg all numbers are unique it should return NA.

I also need the code to be runnable with a union statement with the code I used for the median

Select Code,period,Filedate,inputfile,YearlyOrQuarterly,sortablePeriod,NumericFiledate, 
Count(*) As MonthCounter From TempPivotTable 
Group By Code,period,filedate,inputfile,
YearlyOrQuarterly,sortablePeriod,NumericFiledate
) As MonthCount
Inner Join (
select amount,Code,period,inputfile, Row_Number() Over (Partition By Code,period,inputfile Order By amount) As RowNumber
from TempPivotTable
) As RowCounts
On MonthCount.Code = RowCounts.Code
and MonthCount.period = RowCounts.period
and MonthCount.inputfile = RowCounts.inputfile
Where 2 * RowNumber - MonthCounter Between 0 and 2
Group By MonthCount.Code,MonthCount.period,Filedate,MonthCount.inputfile,YearlyOrQuarterly,sortablePeriod,NumericFiledate
order by MonthCount.Code,MonthCount.period,Filedate,MonthCount.inputfile,YearlyOrQuarterly,sortablePeriod,NumericFiledate

解决方案

I think this is better.

I think this is it.



select code,inputfile,period,Amount,AmountCount,Ranking into #tmp1 from
(
select code,inputfile,period,Amount,count(*) as Amountcount,
Ranking=dense_Rank() over (partition by code,period,inputfile order by count(*) desc)
from TempPivotTable
--where code='USCPIAP' and period='2007' and inputfile='C:\FalconIngest\Input\Final US Long Oct06 input.xls'
group by code,inputfile,period,Amount
) as A

select code,inputfile,period,amount,FirstVendor into #tmp2 from (
select t.code,t.inputfile,t.period,t.amount,min(vendor) as FirstVendor from tempPivotTable t
inner join #tmp1 r
on t.code=r.code
and t.inputfile=r.inputfile
and t.period=r.period
and t.amount=r.amount
 where ranking=1
group by t.code,t.inputfile,t.period,t.amount
) as A


select code,inputfile,period,FirstVendor into #tmp3 from (
select code,inputfile,period,min(Firstvendor) as FirstVendor from #tmp2
group by code,inputfile,period) as A

--where there's no mode set first number=-999999, so program can convert to NA.

select code,vendor,period,Filedate,inputfile,YearlyOrQuarterly,sortablePeriod,numericFiledate,amount,AmountCount into #tmp4 from(
select tp.code,'O' as vendor,tp.period,tp.Filedate,tp.inputfile,tp.YearlyOrQuarterly,tp.sortablePeriod,tp.numericFiledate,t2.amount,t1.AmountCount
from tempPivotTable tp
inner join #tmp1 t1
on tp.code=t1.code and tp.inputfile=t1.inputfile and tp.period=t1.period
inner join #tmp2 t2
on t1.code=t2.code and t1.inputfile=t2.inputfile and t1.period=t2.period and t1.amount=t2.amount
inner join #tmp3 t3
on t2.firstvendor=t3.firstvendor
and tp.vendor=t3.firstvendor
and tp.code=t3.code
and tp.period=t3.period
and tp.inputfile=t3.inputfile
) as A


update #tmp4 set Amount=-999999 where Amountcount=1



drop table #tmp1
drop table #tmp2
drop table #tmp3
drop table #tmp4

这篇关于在SQL 2005中复制Excel模式功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！