那也不对啊 这样是统计每天的总人数了 而我的需求是这个时间段的总人数
何况这样统计出来总人数为68 而实际结果为73
这是MDX的查询:时间不应该放到rows那去 ,如果放到rows那,就成了每天的而我的需求是这个时间段的总人数
WITH MEMBER GTLTCount
AS
(
COUNT
(
FILTER
(
EXISTING
{
[Dim Channel User].[Channel User Key].Members
},
[Measures].[Pay Amount]>= 1000 AND [Measures].[Pay Amount] <= 4999
)
)
)
SELECT
{
GTLTCount
} ON 0,
{
[Dim Date].[Full Date].&[2015-01-01T00:00:00]:[Dim Date].[Full Date].&[2015-01-10T00:00:00]
}
ON 1
FROM [Kysd Statistics V2DB]
sql的:
select '1000-4999' as '付费金额'
,count(channelUserKey) as '人数'
from [dbo].[FactPayUser]
where DateKey<='20150110'
and DateKey>='20150101'
and PayAmount>=1000
and PayAmount<=4999
也不对哦 ,这样是统计每天的总人数了 而我的需求是这个时间段的总人数
时间不应该放到rows那,如果放到那就成了每天的总人数
MDX:何况就算这样查出来总人数也只是68和sql不是同一个值
WITH MEMBER GTLTCount
AS
(
COUNT
(
FILTER
(
EXISTING
{
[Dim Channel User].[Channel User Key].Members
},
[Measures].[Pay Amount]>= 1000 AND [Measures].[Pay Amount] <= 4999
)
)
)
SELECT
{
GTLTCount
} ON 0,
{
[Dim Date].[Full Date].&[2015-01-01T00:00:00]:[Dim Date].[Full Date].&[2015-01-10T00:00:00]
}
ON 1
FROM [Kysd Statistics V2DB]
sql:总人数为73
select '1000-4999' as '付费金额'
,count(channelUserKey) as '人数'
from [dbo].[FactPayUser]
where DateKey<='20150110'
and DateKey>='20150101'
and PayAmount>=1000
and PayAmount<=4999
6 个回复
天桥下的郑成功 - Hadoop大数据开发工程师、数仓架构师、熟悉数据仓库设计、Hadoop、Spark、HBase、Hive、SSIS等开发 2015-01-21 回答
赞同来自:
AS
(
COUNT
(
FILTER
(
EXISTING
{
[Product].[Product].[Product].Members
},
[Measures].[Internet Sales Amount] >= 100 AND [Measures].[Internet Sales Amount] <= 50000
)
)
)
SELECT
{
GTLTCount
} ON 0,
{
[Date].[Date].&[20070101] : [Date].[Date].&[20070110]
}
ON 1
FROM [Adventure Works]
天桥下的郑成功 - Hadoop大数据开发工程师、数仓架构师、熟悉数据仓库设计、Hadoop、Spark、HBase、Hive、SSIS等开发 2015-01-21 回答
赞同来自:
你把产品换成人维度,其他的相应更换 就OK了。
重新Process,就可以自由拖放了
以后别做朋友 2015-01-21 回答
赞同来自:
WITH MEMBER GTLTCount
AS
(
COUNT
(
FILTER
(
EXISTING
{
[Dim Channel User].[Channel User Key].Members
},
[Measures].[Internet Sales Amount] >= 1000 AND [Measures].[Internet Sales Amount] <= 4999
)
)
)
SELECT
{
GTLTCount
} ON 0,
{
[Dim Date].[Full Date].&[2015-01-01T00:00:00]:[Dim Date].[Full Date].&[2015-01-10T00:00:00]
}
ON 1
FROM [Kysd Statistics V2DB]
天桥下的郑成功 - Hadoop大数据开发工程师、数仓架构师、熟悉数据仓库设计、Hadoop、Spark、HBase、Hive、SSIS等开发 2015-01-21 回答
赞同来自:
当然错了。
以后别做朋友 2015-01-22 回答
赞同来自:
何况这样统计出来总人数为68 而实际结果为73
这是MDX的查询:时间不应该放到rows那去 ,如果放到rows那,就成了每天的而我的需求是这个时间段的总人数
WITH MEMBER GTLTCount
AS
(
COUNT
(
FILTER
(
EXISTING
{
[Dim Channel User].[Channel User Key].Members
},
[Measures].[Pay Amount]>= 1000 AND [Measures].[Pay Amount] <= 4999
)
)
)
SELECT
{
GTLTCount
} ON 0,
{
[Dim Date].[Full Date].&[2015-01-01T00:00:00]:[Dim Date].[Full Date].&[2015-01-10T00:00:00]
}
ON 1
FROM [Kysd Statistics V2DB]
sql的:
select '1000-4999' as '付费金额'
,count(channelUserKey) as '人数'
from [dbo].[FactPayUser]
where DateKey<='20150110'
and DateKey>='20150101'
and PayAmount>=1000
and PayAmount<=4999
以后别做朋友 2015-01-22 回答
赞同来自:
时间不应该放到rows那,如果放到那就成了每天的总人数
MDX:何况就算这样查出来总人数也只是68和sql不是同一个值
WITH MEMBER GTLTCount
AS
(
COUNT
(
FILTER
(
EXISTING
{
[Dim Channel User].[Channel User Key].Members
},
[Measures].[Pay Amount]>= 1000 AND [Measures].[Pay Amount] <= 4999
)
)
)
SELECT
{
GTLTCount
} ON 0,
{
[Dim Date].[Full Date].&[2015-01-01T00:00:00]:[Dim Date].[Full Date].&[2015-01-10T00:00:00]
}
ON 1
FROM [Kysd Statistics V2DB]
sql:总人数为73
select '1000-4999' as '付费金额'
,count(channelUserKey) as '人数'
from [dbo].[FactPayUser]
where DateKey<='20150110'
and DateKey>='20150101'
and PayAmount>=1000
and PayAmount<=4999