MDX 中集合的第一个和最后一个测量日期

发布于 2024-07-10 10:12:21 字数 713 浏览 3 评论 0原文

我正在寻找有关如何处理 MDX 查询的一些指导。我的情况是，我有销售发生，它们构成了事实表的颗粒，并且是度量。我有一个产品维度和一个客户维度。我还有一个日期维度和一个时间维度，我将它们分开以保持维度上的成员数量较低。

我正在尝试编写的查询是询问每个客户每个产品的第一次和最后一次购买的查询。因此，示例结果集可能如下所示：

Car - Bob - 2008-12-10 - 15:39 - 2008-12-11 - 16:44
Car - Bill - 2008-12-12 - 09:16 - 2008-12-12 - 09:16
Van - Jim - 2008-12-11 - 14:02 - 2008-12-12 - 22:01

所以，鲍勃买了两辆车，我们有第一次和最后一次购买，比尔买了一辆车，所以第一次和最后一次购买是相同的，吉姆可能买了三辆货车，但我们只显示第一个和最后一个。

我尝试过使用 TAIL，但似乎无法正确显示每个客户的最后一次购买。即便如此，第一次购买的 HEAD 过期表明我无法在同一轴上使用相同的尺寸两次。由于每天可能有多次购买，因此我需要的查询是每个客户对每个产品的最后日期的最后一次，以及每个客户对每个产品的第一个日期的第一次，这也使这变得更加困难。

我不一定要求确切的查询答案，尽管这会有所帮助，但我对使用的方法和最佳方法感兴趣。该平台是 SQL Server Analysis Services 2005。

原文

I'm looking for some guidance on how to approach an MDX query. My situation is that I have sales occuring, which make up the grain of the fact table, and are measures. I have a products dimension and a customer dimension. I also have a date dimension and a time dimension, I made them seperate to keep member counts low on the dimensions.

The query I'm trying to write, is one that asks for the first and last purchase, per customer per product. So, an example result set may look like:

Car - Bob - 2008-12-10 - 15:39 - 2008-12-11 - 16:44
Car - Bill - 2008-12-12 - 09:16 - 2008-12-12 - 09:16
Van - Jim - 2008-12-11 - 14:02 - 2008-12-12 - 22:01

So, Bob bought two cars, and we have the first and last purchases, Bill bought one car so the first and last purchases are the same, Jim may have bought three vans but we only show the first and last.

I've tried using TAIL, but can't seem to get the sets correct to show the last purchase per customer. Even then, expirements with HEAD for the first purchase showed I couldn't use the same dimension twice on the same axis. It's also made harder by the fact that there may be several purchases per day, so the query I need is the last time for the last date for each customer for each product, and the first time for the first date for each customer for each product.

I'm not neccesarily asking for an exact query answer, although that would help, but I am interested in the approach and best methods to use. The platform is SQL Server Analysis Services 2005.

分享到QQ

分享到微博