Extra SQL in simple line chart query causing wrong results #18318
Replies: 4 comments
-
How do you want it visualized? |
Beta Was this translation helpful? Give feedback.
-
You don't have to add |
Beta Was this translation helpful? Give feedback.
-
I got it to work by first creating new metrics in the dataset used by the report and removing the group by setting.
Using a Sort/Order by clause is old habit for me, since 1991, because it's never been taken for granted. Until now, I've never seen a data analysis product not have an order by clause in the generated sql.
Thanks. Kevin
On Sunday, August 8, 2021, 6:18:05 AM PDT, kaspergaj ***@***.***> wrote:
How do you want it visualized?
I think the x-axis in linecharts has to be dates, so the _timestamp should correspond to the time at meal_date with a pandas representation.
What do you wish to achieve with the sort by column? Only show the highest 100 results?
My guess is you can achieve your desired chart by removing meal_date in Group By and leaving it empy. Maybe also keeping the Sort By box empty.
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or unsubscribe.
Triage notifications on the go with GitHub Mobile for iOS or Android.
|
Beta Was this translation helpful? Give feedback.
-
ok, I'm just starting out w/ 1.2 so apparently it was different in previous versions.
Thanks. Kevin
On Sunday, August 8, 2021, 7:51:48 PM PDT, Yongjie Zhao ***@***.***> wrote:
You don't have to add meal_date to group by control anymore. Time column controlled by "Time" pane which above Query pane.
—
You are receiving this because you authored the thread.
Reply to this email directly, view it on GitHub, or unsubscribe.
Triage notifications on the go with GitHub Mobile for iOS or Android.
|
Beta Was this translation helpful? Give feedback.
-
I'm working on a simple line chart which has 1 time-based column and 1 fact/metric. I've attached a screen capture of the chart dialog screen. I have 1 time column (meal_date), 1 metric sum(calories), 1 group by expression (meal_date), and 1 sort by expression (1) defined on the data tab.
This should yield a sql query like this:
select meal_date AS meal_date,
sum(calories) AS "Calories"
from public.factfood
group by meal_date
order by 1;
Very simple, very straightforward. Even the line chart itself looks ok. However there's no data displayed on the line chart. Additionally, there is an extra column called _timestamp and the result set is not sorted at all. Upon viewing the sql created, there's a lot of extra code that I would not expect to be there. Here is the sql query code created for this line chart:
SELECT meal_date AS timestamp, <-- Extra/Duplicate Column, not referenced in the dialog screen
meal_date AS meal_date,
sum(calories) AS "Calories"
FROM public.factfood
JOIN <-- Completely unneccessary, this is a 1 table only query, so no joins needed
(SELECT meal_date AS meal_date,
sum(calories) AS mme_inner__
FROM public.factfood
GROUP BY meal_date
ORDER BY 1 DESC **<-- Order by in the wrong place, it needs to be below the last group by **
LIMIT 100) AS anon_1 ON meal_date = meal_date__
GROUP BY meal_date,
meal_date
LIMIT 10000;
Environment
Centos 8 o/s
Postgres 13 DB
1.2
3.8
How do I fix these 3 errors?
I need to not have the _timestamp column but I don't see where it comes from. There's no option to not have a Time column.
I need to have the order by clause utilized by the correct select stmt. Getting rid of the unnecessary join/select stmts would help.
I need to see the datapoints on the chart. This is probably because of that extra _timetamp column, not sure.
Beta Was this translation helpful? Give feedback.
All reactions