You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Describe the bug
When utilizing the share expression in Metabase.
It generates a decimal value representing a percentage, the problem is cube converts assumes the as an int so the Share result expression is always either 0 or 1 rendering it pretty much useless. Changing the casting manually to enforce the usages of ::double resolves the issue.
DATE_TRUNC('week', "source"."created_date") AS "created_date",
SUM(
CASE
WHEN "source"."count" = 1 THEN 1
ELSE 0.0
END
) / COUNT(*) AS "one video"
Manually casting either side of these expressions to ::double resolves the problem IE:
SUM(
CASE
WHEN "source"."count" = 1 THEN 1
ELSE 0.0
END
) / COUNT(*)::double AS "one video"
Full SQL:
SELECT
DATE_TRUNC('week', "source"."created_date") AS "created_date",
SUM(
CASE
WHEN "source"."count" = 1 THEN 1
ELSE 0.0
END
) / COUNT(*) AS "one video"
FROM
(
SELECT
DATE_TRUNC('week', "public"."table"."created_date") AS "created_date",
"public"."table"."user_id" AS "user_id",
count(distinct "public"."table"."video_id") AS "count"
FROM
"public"."table"
WHERE
(
"public"."table"."created_date" >= DATE_TRUNC('quarter', NOW())
)
AND (
"public"."table"."created_date" < DATE_TRUNC('quarter', (NOW() + INTERVAL '3 month'))
)
GROUP BY
DATE_TRUNC('week', "public"."table"."created_date"),
"public"."all_videos"."user_id"
ORDER BY
DATE_TRUNC('week', "public"."table"."created_date") ASC,
"public"."table"."user_id" ASC
) AS "source"
GROUP BY
DATE_TRUNC('week', "source"."created_date")
ORDER BY
DATE_TRUNC('week', "source"."created_date") ASC
=
The text was updated successfully, but these errors were encountered:
I assume this is not a bug but rather Cube conforming to the SQL standard (and, more specifically, it's implementation in Postgres). As you can see in the Postgres documentation, "(integer division truncates results." If you'd like to use floating-point division, at least one of the arguments should be floating-point. Casting them, either on the data model side or the querying side, is a way to go here.
I see. That makes sense! The workaround here would be to multiple the whole CASE expression by 1.0. However, I believe we'll conform with the Postgres implementation eventually.
igorlukanin
added
bug
Something isn't working
and removed
question
The issue is a question. Please use Stack Overflow for questions.
labels
Jun 26, 2024
Describe the bug
When utilizing the share expression in Metabase.
It generates a decimal value representing a percentage, the problem is cube converts assumes the as an
int
so theShare
result expression is always either 0 or 1 rendering it pretty much useless. Changing the casting manually to enforce the usages of::double
resolves the issue.Latest Cube release
Driver: Redshift
CUBE_SQL_PUSH_DOWN: enabled
Problem SQL
Manually casting either side of these expressions to
::double
resolves the problem IE:Full SQL:
The text was updated successfully, but these errors were encountered: