MDX 查询用于计算符合特定条件的行数(每个问题、客户组的最新行)
我有以下事实表:
response_history_id client_id question_id answer
1 1 2 24
2 1 2 27
3 1 3 12
4 1 2 43
5 2 2 39
它保存了客户对某些问题的回答的历史记录。每个 client_id、question_id 组合的最大 response_history_id 是该问题和客户端的最新答案。
我想要做的是计算最新答案落在特定范围内的客户数量
我有一些维度:
question associated with question_id
client associated with client_id
response_history_id associated with response_history_id
range associated with answer. 0-20 low, 20-40 = medium, >40 is high
和一些度量:
max_history_id as max(response_history_id)
clients_count as disticnt count(client_id)
现在,我只想分组按范围列出的最新答案:
select
[ranges].members on 0,
{[Measures].[clients_count]} on 1
from (select [question].[All].[2] on 1 from [Cube])
我得到的是:
Measures All low medium high
clients_count 2 0 2 1
但我想要的(并且我无法得到)是基于最新答案的计算:
Measures All low medium high
clients_count 2 0 1 1
我明白为什么我的查询没有给我想要的结果,它更多的是用于演示目的。但我尝试了很多更复杂的 MDX 查询,仍然无法得到任何好的结果。
另外,我无法从事实表生成静态视图,因为稍后我想限制事实表中另一列(即时间戳)的搜索,我的查询最终必须能够获取最新答案的客户端数量给定时间戳之前的问题属于特定范围。
有人可以帮我解决这个问题吗? 我可以定义其他维度和度量,并且我正在使用 iccube。
I have the following fact table:
response_history_id client_id question_id answer
1 1 2 24
2 1 2 27
3 1 3 12
4 1 2 43
5 2 2 39
It holds history of client answers to some questions. The largest response_history_id for each client_id,question_id combination is the latest answer for that question and client.
What I want to do is to count the number of clients whose latest answer falls within a specific range
I have some dimensions:
question associated with question_id
client associated with client_id
response_history_id associated with response_history_id
range associated with answer. 0-20 low, 20-40 = medium, >40 is high
and some measures:
max_history_id as max(response_history_id)
clients_count as disticnt count(client_id)
Now, I want to group only the latest answers by range:
select
[ranges].members on 0,
{[Measures].[clients_count]} on 1
from (select [question].[All].[2] on 1 from [Cube])
What I get is:
Measures All low medium high
clients_count 2 0 2 1
But what I wanted (and I can't get) is the calculation based on the latest answer:
Measures All low medium high
clients_count 2 0 1 1
I understand why my query doesn't give me the desired result, it's more for demonstration purpose. But I have tried a lot of more complex MDX queries and still couldn't get any good result.
Also, I can't generate a static view from my fact table because later on I would like to limit the search by another column in fact table which is timestamp, my queries must eventually be able to get _the number of clients whose latest answer to a question before a given timestamp falls within a specific range.
Can anyone help me with this please?
I can define other dimensions and measures and I am using iccube.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论