如何在某些条件下获得COUNT个NON-NULL成员? [英] How to get a COUNT of NON-NULL members in a set with some conditions?

问题描述

为了使您对问题有所了解，有些用户"执行某些任务"，这些任务"被分类为各种类型.一种这样的类型是"DSR".我面临的要求是查找 2013年每个月用户ID 033343处理了DSR类型的请求有多少.
我需要的是获取用户(Userid = 033343)在year = 2013年中处理的所有不同请求中的成员数，并且对于Request Type = DSR. 以下是我最初尝试的方法:

To give you a slight background to the problem, there are certain "users" who do certain "tasks" which are categorized into various types; one such type being "DSR". The requirement I am faced with is to find how many requests of type DSR the userid 033343 worked on for each month of year 2013.
What I would be needing is to get the count of the members in set of all the distinct requests a user (Userid = 033343) worked on in the year = 2013 and for the Request Type = DSR. Below is what I tried at first:

WITH SET requests AS
EXISTING [Request].[RequestID].MEMBERS

MEMBER Measures.[Count033343] AS
DISTINCTCOUNT(requests)

SELECT Measures.[Count033343] ON 1,
[Calendar].[CalendarMonthName].CHILDREN ON 0 /*Values like January, February, etc. Not tied to Calendar Years*/
FROM [Model]
WHERE(
                 [User].[UserID].&[033343],
                 [Request Type].[Request Type Name].&[DSR],
                 [Calendar].[CalendarYear].&[2013]

      )

但这没用.我没有得到结果.

But that didn't work. I got below result.

我认为，我正在构建的布景存在问题.

I figured, there is an issue with the set that I was building.

然后将MDX移至Set定义.我认为，与在WHERE子句中有一组额外的元组相比，这可能会使代码工作更快.

Then I moving the MDX to the Set definition. I thought it might make the code work faster as compared to having an extra set of tuple in WHERE clause.

WITH SET requests AS
EXISTS([Request].[RequestID].MEMBERS, (
                     [User].[UserID].&[033343],
                     [Request Type].[Request Type Name].&[DSR],
                     [Calendar].[CalendarYear].&[2013]
      ))

MEMBER Measures.[Count033343] AS
DISTINCTCOUNT(requests)

SELECT Measures.[Count033343] ON 1,
[Calendar].[CalendarMonthName].MEMBERS ON 0
FROM [Model]

但是我仍然得到与上面相同的结果- 0等于所有月份的计数！

But I was still getting the same result as above - 0 as the count for all the months!

然后，我想到检查自己正在构建的集合(在第二个示例中)

Then, I thought of checking the set itself I was building(In the second example)

WITH SET requests AS
EXISTS([Request].[RequestId].MEMBERS, (
                     [User].[UserID].&[033343],
                     [Request Type].[Request Type Name].&[DSR],
                     [Calendar].[CalendarYear].&[2013]
      ))

SELECT [Measures].[Average of Late Tasks] ON 1,
requests ON 0
FROM [Model]

那证明了两件事.

1)集合返回NULL值以及非null值.

1) The set was returning NULL values along with non null values.

2)实际上从集合中返回了请求ID.因此，对于指定的元组，请求数绝对大于0.

2) There are indeed Request Ids returned from the set. So the count of Requests is definitely greater than 0 for the tuple specified.

我可以使用NON EMPTY子句删除NON NULL值，但是我不想使用它，因为我读到它会增加开销.

I could use the NON EMPTY clause to remove the NON NULL values but, I don't want to use it as I read that it adds overhead.

正如我上面已经验证的那样，2013年的所有月中确实有针对各种用户的请求，为什么我要进行计数为0?我要去哪里错了?为什么EXISTS和EXISTING功能不能按预期运行?集合是否不应该仅返回针对条件 存在的那些请求ID?我的理解错了吗?

As I have verified above that there are indeed requests for various users for all the months in 2013, why I am getting the count to be 0? Where am I going wrong? Why isn't the EXISTS and EXISTINGfunctionality working as expected? Isn't the set supposed to return only those Request IDs which exist for the condition? Is my understanding wrong?

编辑

对于@whytheq-在您的最终查询中，我得到了非零值

For @whytheq - With your final query, I got non-zero values

WITH MEMBER Measures.[x] AS
   Count(
     NONEMPTY(
       [Request].[RequestId].MEMBERS
       , {([Measures].[Average of Late Tasks])}
     )      
   )
SELECT 
   Measures.[x] ON 0,
   [Calendar].[CalendarYear].&[2013] ON 1 
FROM [Model]

输出

       x
2013   2

推荐答案

(未测试)，它是否仍返回零?

(Not tested) Does this still return zeros?

WITH SET [requests] AS
  {([User].[UserID].&[033343],
  [Request Type].[Request Type Name].&[DSR])}
  * {[Measures].[Average of Late Tasks]}  //<<<<try running with and without this line
  * [Request].[RequestID].MEMBERS
MEMBER Measures.[Count033343] AS
  DISTINCTCOUNT(requests)
SELECT 
  [Calendar].[CalendarMonthName].CHILDREN ON 0,
  Measures.[Count033343] ON 1 
FROM [Model]
WHERE(
      [Calendar].[CalendarYear].&[2013]
      ) 

以下内容如何?

WITH 
  MEMBER Measures.[Count033343] AS
    DISTINCTCOUNT({[Request].[RequestID].[RequestID]})
SELECT 
  [Calendar].[CalendarMonthName].CHILDREN ON 0,
  Measures.[Count033343] ON 1 
FROM [Model]
WHERE(
      [Calendar].[CalendarYear].&[2013],
      [User].[UserID].&[033343],
      [Request Type].[Request Type Name].&[DSR]
      ) 

也许首先尝试这个更简单的查询，然后(如果可行！)我们可以以此为基础来尝试查找问题...

Maybe try this simpler query first and (if it works!) we can build up from this to try to locate the problem...

WITH MEMBER Measures.[x] AS
   Count(
     NONEMPTY(
       [Request].[RequestId].MEMBERS
       , {([Measures].[Average of Late Tasks])}
     )      
   )
SELECT 
   Measures.[x] ON 0,
   [Calendar].[CalendarYear].&[2013] ON 1 
FROM [Model]

这篇关于如何在某些条件下获得COUNT个NON-NULL成员?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！