商业智能 - 找不到我正在寻找的价值

发布于 2024-12-03 19:14:52 字数 474 浏览 1 评论 0原文

我正在为我们的网站设置一个 SSAS 项目,但我无法找到好的价值,而它在普通 SQL 查询中非常简单。

这是我的设置:我的网站上有一个充满用户连接事实的数据仓库。所以我有一个成员维度和一个日期维度。这是我正在寻找的 KPI:“用户平均访问我们网站的天数”。

让我们举个例子:

Member    Day
a         1
a         1
a         2
b         2
a         4
a         5
b         5
a         6

在这种情况下,KPI 应为 3.5(a=5,b=2)。在普通 SQL 中,我会对一个 group by 进行平均(这是我想到的第一个请求,也许有更好的请求)。

但当我尝试将维度和事实组合在一起时,我找不到正确的衡量标准。

我在寻找错误的东西吗?我应该放弃我的 SQL 思维方式吗?您将如何获得我需要的价值?

I'm setting up a SSAS project for our websites but I can't managed to find the good value whereas it's quite simple in plain SQL query.

Here's my setup : I have a datawarehouse filled with user connection fact on my sites. And so I have a Member dimension and a date dimension. Heres's the KPI I'm loking for : "For how many average days a user is coming to see our site".

Let's take an example :

Member    Day
a         1
a         1
a         2
b         2
a         4
a         5
b         5
a         6

In this case the KPI should give 3,5 (a=5, b=2). In plain SQL I would have done an average on a group by on a group by (it's the first request I've got in mind, maybe there's a better one).

But as soon as I try to assemble dimension and facts together I can't find the right measure.

Am I looking for the wrong thing ? Should I abandon my SQL way of thinking ? How would you do to get the value I need ?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

小ぇ时光︴ 2024-12-10 19:14:52

我现在明白了!这只是一个国际化问题。对我来说 3,5 意味着数字 35,我会把它写成 3.5 :)

SELECT
  AVG(CountOfDay) As AverageDays
FROM
  (SELECT Member, COUNT(DISTINCT Day) CountOfDay FROM YourTable GROUP BY Member) AS UniqueDaysByMember

真的您不需要在子查询的 SELECT 中使用“Member”。它只是让它对我来说“有意义”,所以当我稍后回来查看代码时我不会感到困惑!

I understand now! It was just an internationalisation problem. To me 3,5 means the numbers 3 and 5, I'd write it as 3.5 :)

SELECT
  AVG(CountOfDay) As AverageDays
FROM
  (SELECT Member, COUNT(DISTINCT Day) CountOfDay FROM YourTable GROUP BY Member) AS UniqueDaysByMember

Really you don't need "Member" in the sub-query's SELECT. It just makes it "mean" something to me, so I don't get confused if I come back and look at the code later!

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文