8 ms·

I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqu

I like SQL when I'm not writing reporting queries. GROUP BYs bite me (With MySQL 8 I end up reaching for the ANY_VALUE() function), and I end up with more subqueries than I feel I should need. When working with time-indexed data I feel I'm forcing the database to do something it doesn't want to. E.g. if I want to answer the query "How many sales are there per day this month?" and I want an entry for every day in the month, even when there were zero sales. Or another query asking "Which days didn't have sales?" and listing the days. I haven't found a way to do this in-database. I end up answering in code based on the data I fetch. Definitely feel it's me not SQL, but not found the answer.

sk5t 7y ago
A more functional mindset can definitely help here. Think of your "sales per day" model in terms of starting with a sequence of days--startingDay up to startingDay + n--as the input to a function that maps to an aggregate of that day's activity. Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."
- pbowyer 7y ago
  I find thinking about analytic queries like these as map/filter/reduce easier than thinking in terms of the SQL!
  - taffer 7y ago
    SQL and functional programming constructs are actually quite similar: SELECT -> map FROM -> stream JOIN -> flatMap WHERE -> filter GROUP BY -> collect ORDER BY -> sorted https://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivalents-in-java-8-streams/ https://blog.jooq.org/2015/08/13/common-sql-clauses-and-thei...
    - karmakaze 7y ago
      I don't see how JOIN is like flatMap. It's a subset of a cross-product. It's more like do-notation: x <- .. y <- .. return (x, y)
      - taffer 7y ago
        > I don't see how JOIN is like flatMap This is explained in the blog post I linked, including an example.
        karmakaze 7y ago
        It's really flatMap/map from the link: List<Integer> s1 = Stream.of(1, 2); Supplier<Stream<String>> s2 = ()->Stream.of("A", "B"); s1.flatMap(v1 -> s2.get() .map(v2 -> tuple(v1, v2))) .forEach(System.out::println); That's just a nested loop.
JamesSwift 7y ago
The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting database). Once you reimagine the data in the OLAP style then these kinds of queries are simplistic. EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.
- pbowyer 7y ago
  Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that. For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.
  - JamesSwift 7y ago
    Yes, the time table is a pregenerated set of all possible moments in time for whatever resolution you care about. So, for example, if you cared about day resolution it might be date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short ... 20200101 2020 1 1 1 1 january jan 20200102 2020 1 2 1 1 january jan ... 20200401 2020 4 1 2 14 april apr ... You would FK on the `date_key` and add as many columns as you need to support querying against the dimension. I also like to add a proper `datetime` representation of the date so I can easily do a date range query.
  - jacques_chester 7y ago
    I found Kimball's book to be interesting and helpful. I think the Kimball approach gets bogged down in temporal evolution (Slowly Changing Dimension type 1? 2? 3? 4? 5?), but that's more about the underlying absence of meaningful bitemporalism than the dimensional schema approach per se.
EvanAnderson 7y ago
Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sales. Set 1 could be a "numbers table" or generated with something like "generate_series()" in PostgreSQL. Set 2 is made by summarizing the data in a sales table by date using "GROUP BY" and "SUM". Then you're just looking at JOIN'ing those sets and COAELSCE'ing the NULL returned from days when there are no sales into 0.
- pbowyer 7y ago
  > Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :) Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted the last 60, 90 or 120 days? I suppose you could first create a temporary table to be the "numbers table", custom made for the range you want to fetch - is there an alternative?
  - EvanAnderson 7y ago
    In many databases you would create a function to fill the role of that "numbers table" and, effectively, map that function onto rows. That function can express whatever "richness" you need (i.e. civil calendar month, phase of the moon, etc... >smile<) that might not be easily expressed as a series.
  - will_pseudonym 7y ago
    There are date functions in most SQL packages which can create the limiting factors you're looking for, just as in procedural languages. Once you have the list of dates you're interested in, the problem becomes simple.
  - Amezarak 7y ago
alexilliamson 7y ago
You can (IMO) pretty succinctly enumerate all the days in a month with a recursive CTE. The example in the following link does it for weekdays, but it's the same idea: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/ https://www.sqlservertutorial.net/sql-server-basics/sql-serv...
mulmen 7y ago
For anything other than the absolute simplest case you should have two databases. One for OLTP (the application(s)) and one for OLAP (the reporting). If your reporting queries are hard or complex it's because you did a bad job architecting the reporting tables. Reporting queries should almost always be the simplest form of query if you designed your warehouse properly. Days with sales is easy with a simple SUM or COUNT and GROUP BY. Days without sales is easy if you think about this dimensionally. SELECT dim_days.day_of_month, COUNT(fact_orders.order_id) /* Select from dim_days first because you want every day. */ FROM dim_days /* Outer join to the fact table to pull in the data you have and add that to your dates. */ LEFT JOIN fact_orders ON dim_days.calendar_day = fact_orders.order_day /* Filter results by the desired range. */ WHERE dim_days.month = 4 AND dim_days.year = 2020 GROUP BY dim_days.day_of_month; As I continue to say: "SQL is easy. Data is hard."

sk5t 7y ago

A more functional mindset can definitely help here. Think of your "sales per day" model in terms of starting with a sequence of days--startingDay up to startingDay + n--as the input to a function that maps to an aggregate of that day's activity. Aggregate functions in SQL are IMHO quite awesome once you develop a comfort level to stop worrying about them per se. I wouldn't like to try to get Excel to tell me--or write the code to do manually--something like "show me the standard deviation in units sold by day of week over the last ten summers."

pbowyer 7y ago
I find thinking about analytic queries like these as map/filter/reduce easier than thinking in terms of the SQL!
- taffer 7y ago
  SQL and functional programming constructs are actually quite similar: SELECT -> map FROM -> stream JOIN -> flatMap WHERE -> filter GROUP BY -> collect ORDER BY -> sorted https://blog.jooq.org/2015/08/13/common-sql-clauses-and-their-equivalents-in-java-8-streams/ https://blog.jooq.org/2015/08/13/common-sql-clauses-and-thei...
  - karmakaze 7y ago
    I don't see how JOIN is like flatMap. It's a subset of a cross-product. It's more like do-notation: x <- .. y <- .. return (x, y)
    - taffer 7y ago
      > I don't see how JOIN is like flatMap This is explained in the blog post I linked, including an example.
      - karmakaze 7y ago
        It's really flatMap/map from the link: List<Integer> s1 = Stream.of(1, 2); Supplier<Stream<String>> s2 = ()->Stream.of("A", "B"); s1.flatMap(v1 -> s2.get() .map(v2 -> tuple(v1, v2))) .forEach(System.out::println); That's just a nested loop.

JamesSwift 7y ago

The answer is likely that your storage schema is incorrect. You have things stored in OLTP (i.e. app database) but trying to read it as OLAP (i.e. reporting database). Once you reimagine the data in the OLAP style then these kinds of queries are simplistic. EDIT: specifically for your example, in an OLAP style you would generate a Times table and then foreign key the sales table to it based on the date. Then you can easily query against that Times table as the filter/bucket for your queries.

pbowyer 7y ago
Yes, agreed. It was designed for OLTP not OLAP, and I have to get my mindset into that. For days as my example has, would the Time table be generated for say 1970-2050? Given months are of different lengths and there are leap years, I'm assuming this is needed, rather than storing a single year.
- JamesSwift 7y ago
  Yes, the time table is a pregenerated set of all possible moments in time for whatever resolution you care about. So, for example, if you cared about day resolution it might be date_key | year_num | month_num | day_num | quarter_num | week_num | month_name | month_name_short ... 20200101 2020 1 1 1 1 january jan 20200102 2020 1 2 1 1 january jan ... 20200401 2020 4 1 2 14 april apr ... You would FK on the `date_key` and add as many columns as you need to support querying against the dimension. I also like to add a proper `datetime` representation of the date so I can easily do a date range query.
- jacques_chester 7y ago
  I found Kimball's book to be interesting and helpful. I think the Kimball approach gets bogged down in temporal evolution (Slowly Changing Dimension type 1? 2? 3? 4? 5?), but that's more about the underlying absence of meaningful bitemporalism than the dimensional schema approach per se.

EvanAnderson 7y ago

Your example is a fun one and not too hard to do. Thinking about the intersection of sets is important for grokking what you're trying to do in that example (and with SQL in general). For me, at least, SQL suddenly made a lot of sense when the idea that I was working with intersections and subsets of sets. In your example you have (1) a set of all dates in a range, and (2) a set of sales totals for days that had sales. Set 1 could be a "numbers table" or generated with something like "generate_series()" in PostgreSQL. Set 2 is made by summarizing the data in a sales table by date using "GROUP BY" and "SUM". Then you're just looking at JOIN'ing those sets and COAELSCE'ing the NULL returned from days when there are no sales into 0.

pbowyer 7y ago
> Set 1 could be a "numbers table" or generated with something like "generate_series()" I've done it this way when doing it per-minute or per-hour - i.e. for 60 minutes or 24 hours; fixed, constant ranges. I picked this example because (to me at least) it's more difficult :) Calendar months have different lengths, so something would have to tell it which month to use (and account for leap years). But say you wanted the last 60, 90 or 120 days? I suppose you could first create a temporary table to be the "numbers table", custom made for the range you want to fetch - is there an alternative?
- EvanAnderson 7y ago
  In many databases you would create a function to fill the role of that "numbers table" and, effectively, map that function onto rows. That function can express whatever "richness" you need (i.e. civil calendar month, phase of the moon, etc... >smile<) that might not be easily expressed as a series.
- will_pseudonym 7y ago
  There are date functions in most SQL packages which can create the limiting factors you're looking for, just as in procedural languages. Once you have the list of dates you're interested in, the problem becomes simple.
- Amezarak 7y ago

alexilliamson 7y ago

You can (IMO) pretty succinctly enumerate all the days in a month with a recursive CTE. The example in the following link does it for weekdays, but it's the same idea: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/ https://www.sqlservertutorial.net/sql-server-basics/sql-serv...

mulmen 7y ago

For anything other than the absolute simplest case you should have two databases. One for OLTP (the application(s)) and one for OLAP (the reporting). If your reporting queries are hard or complex it's because you did a bad job architecting the reporting tables. Reporting queries should almost always be the simplest form of query if you designed your warehouse properly. Days with sales is easy with a simple SUM or COUNT and GROUP BY. Days without sales is easy if you think about this dimensionally. SELECT dim_days.day_of_month, COUNT(fact_orders.order_id) /* Select from dim_days first because you want every day. */ FROM dim_days /* Outer join to the fact table to pull in the data you have and add that to your dates. */ LEFT JOIN fact_orders ON dim_days.calendar_day = fact_orders.order_day /* Filter results by the desired range. */ WHERE dim_days.month = 4 AND dim_days.year = 2020 GROUP BY dim_days.day_of_month; As I continue to say: "SQL is easy. Data is hard."