Time comparison does not always capture the correct data when the result set is limited about superset HOT 11 OPEN

@eschutho BTW, I'll open the SIP as soon as possible in the week.

from superset.

In the above example, I am trying to fetch the top 10 years from the years 1989 - 2004 where the name Alex was the most popular and compare that count to the period 10 years prior.

@eschutho I think you misunderstand the "TOK N" analytics meaning, and what is "order by" and "limit" clause in SQL meaning.

The first screenshot from your post in the explore page will ask sort by the count, and then only retrieve the top 10 rows. If you really want to fetch the top 10 years, you should set ds as value of SORT BY control.

from superset.

Hi, thanks @zhaoyongjie. Apologies, but the last query that I showed as the correct example I should have sorted by count instead of by date. That may be where the confusion came from. I updated it in my example. But essentially, I'm looking to sort by the count, so in this case in 1995 there were 12 instances, and that row should be first. I want the top ten years where the name had the highest count. This is the corrected version:

from superset.

so in this case in 1995 there were 12 instances, and that row should be first. I want the top ten years where the name had the highest count. This is the corrected version:

it should be another topic that how to sort the result set after fetched the data from database, there is a sort operator in the post processing for it.

Your SQL have some issues:

1. The order by and limit clause should combine together always, it means getting "top N" records from the table. The subquery from your post didn't provide limit clause in subquery.
2. The SQL and the user interface(Explore page) are inconsistent, "top ten years" was selected in the UI, then the SQL clause should be order by ds limit 10 not only in subquery, but also in the outer query.
3. Significant extra computing, 10 records dataset join <---> unpredictable records.

As the mention before, if you really need a secondary sort by, the sort operator might help.

from superset.

1. ... The subquery from your post didn't provide limit clause in subquery.
2. Significant extra computing, 10 records dataset join <---> unpredictable records.

Including that in the subquery would truncate the set used to JOIN correct? Shouldn't we leverage the DBMSs optimizations when executing the query as a whole instead? Otherwise we end up having incomplete result sets if for example the LIMIT is too low, as as stated before data inconsistency is something we should never aim for.

To tackle this bug I would say that in general we have two limitations depending on which database are we working on:

1. Not all databases support CTE
2. Not all databases support JOINS

For the first one we would have to write something that check whether or not CTE are supported, if it does, make use of it, if not, write the query manually.

For the second, if the database supports joins we should ensure data consistency at pre-query execution with the outer joins, if not supported we should fallback to DataFrame joins at post processing time, but again, just as a fallback because data inconsistency might arise.

from superset.

@Antonio-RiveroMartnez
Upon rereading this issue, I believe the confusion lies in understanding the 'limit' control and the 'sort by' control in the UI, as well as how to apply them in SQL.

1. The value of the 'limit' control is 10, and the 'sort by' control is 'DS'. This indicates sorting by the 'DS' column and then returning the top 10 records.
2. The value of the 'limit' control is 10, and the 'sort by' control is 'count'. This indicates sorting by the 'count' column (metric) and then returning the top 10 records.

The logic is included in all visualizations. Currently, there isn't a control to specify how to sort a column as a secondary sort by. In other words, Superset doesn't have a way to define sorting by DS and returning the top 10, then sorting by count and returning the top 10. I also posted the result in the comment. The result remains the same whether you use Pandas join or SQL join.

from superset.

1. Not all databases support CTE
2. Not all databases support JOINS

It's not only compatibility about CTE and JOIN, but also how to how to define time delta in different DB.

from superset.