DB2 - 按时间范围对数据进行排名
我正在尝试编写一份报告（Solaris 上的 DB2 9.5）来执行以下操作：
我有一组数据，假设它是一个订单表。我想要运行一份报告，该报告将提供每个月每个客户的订单数量以及他们当月的“排名”。排名将基于订单数量。我正在使用 RANK() OVER 子句，但我似乎无法让它给我每月排名（或其他“分组依据”）。如果有 100 个客户和 12 个月的数据，我预计报告中有 1200 行，每月 100 行，每行的排名在 1 到 100 之间。请告诉我更多详细信息是否有帮助。提前致谢。
I am trying to write a report (DB2 9.5 on Solaris) to do the following:
I have a set of data, let's say it's an order table. I want to run a report which will give me, for each month, the number of orders per customer, and their "rank" that month. The rank would be based on the number of orders. I was playing around with the RANK() OVER clauses, but I can't seem to get it to give me a rank per month (or other "group by"). If there are 100 customers and 12 months of data, i would expect 1200 rows in the report, 100 per month, each with a rank between 1 and 100. Let me know if more detail would be helpful. Thanks in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
解决方案是使用 PARTITION BY 子句。
例如，请参见第 5 页：http://cmsaville.ca/documents/MiscDocs/TopNQueries。 pdf
the solution is to use the PARTITION BY clause.
for example, see page 5 here: http://cmsaville.ca/documents/MiscDocs/TopNQueries.pdf