SQL语句使用GROUP或RANK中的WHERE [英] SQL statement using WHERE from a GROUP or RANK

问题描述

我有大约35,000行的销售快照。我们来调用列：

 销售代表|帐户ID |总合同价值|日期
  

我需要按照 Sales Rep 然后从那里，根据合同总价值选择 Sales Rep 的前35个账户。 <2013年1月的月份（日期）> 合同总额>> $ 10,000。

例如，从一月份开始，John Doe在这个表中有294个账户，我只想看到他的前35个账户> = 10,000美元，与Jane Doe相同，等等等等。查询在资源使用方面尽可能高效非常重要。

想法？

解决方案

为此，您希望使用一个名为 row_number（）的函数：

（选择ss。*，row_number（）over（由salesrep order by ContractValue desc分区）从快照ss $中选择
b $ b其中，TotalContractValue> = 10000且'2013-01-01'和'2013-01-31'之间的日期
）ss
其中seqnum <= 35

您不指定您正在使用的数据库。在没有 row_number（）的数据库中，有些替代品效率较低。

I have a sales snapshot with about 35,000 rows. Let's call the columns:

Sales Rep | Account ID | Total Contract Value | Date

I need to group everything by Sales Rep and then from there, select that Sales Rep's top 35 accounts based off of Total Contract Value where the Total Contract Value is >= $10,000 for the Month (Date) of January 2013.

So for example, say John Doe had 294 accounts in this table from January, I only want to see his top 35 accounts >= $10,000 , same for Jane Doe, etc. etc. It's very important that the query be as efficient in it's resource usage as possible.

Thoughts?

解决方案

For this, you want to use a function called row_number():

select ss.*
from (select ss.*, row_number() over (partition by salesrep order by ContractValue desc) as seqnum
      from snapshot ss
      where TotalContractValue >= 10000 and date between '2013-01-01' and '2013-01-31'
     ) ss
where seqnum <= 35

You don't specify the database you are using. In databases that don't have row_number(), there are alternatives that are less efficient.

这篇关于SQL语句使用GROUP或RANK中的WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！