Fork me on GitHub
#sql
<
2019-03-01
>
caleb.macdonaldblack01:03:09

I have some function calls that a repeated in my select statement and group-by statement. Is there anyway to reuse this Kind of like a let block? I'm using honey-sql however knowing how to do this in sql first is a good start

caleb.macdonaldblack01:03:17

For example

(-> (select (->> (sql/call :/ :unix-timestamp-ms 1000)
                 (sql/call :to_timestamp)
                 (sql/call :date_trunc "year"))
            (sql/call :sum :grid-to-site-pre-calc-wh))
    (from :time-series)
    (where [:= :device-id "caleb-test-device-25"])
    (group :device-id
           :scenario-id
           (->> (sql/call :/ :unix-timestamp-ms 1000)
                (sql/call :to_timestamp)
                (sql/call :date_trunc "year")))
    sql/format)

caleb.macdonaldblack01:03:37

I guess I could actually just use a let block:

(let [trunc-year (->> (sql/call :/ :unix-timestamp-ms 1000)
                      (sql/call :to_timestamp)
                      (sql/call :date_trunc "year"))]
  (-> (select trunc-year
              (sql/call :sum :grid-to-site-pre-calc-wh))
      (from :time-series)
      (where [:= :device-id "caleb-test-device-25"])
      (group :device-id
             :scenario-id
             trunc-year)
      sql/format))
Is there any way to do this in SQL? Just out of curiosity?

seancorfield01:03:23

@caleb.macdonaldblack You could probably do this with CTEs in SQL -- which will depend on which DB you're using, which version, and which driver.

seancorfield01:03:48

Otherwise, this is exactly the benefit of HoneySQL: reusable, composable query fragments.

caleb.macdonaldblack01:03:07

Yeah I'm loving honeySql for the fact that I can do that

seancorfield01:03:49

I've no idea about PostgreSQL, nor CTEs, unfortunately. We use MySQL (Percona) and CTEs only came in 8.x (and we're still on 5.7).

caleb.macdonaldblack01:03:11

Nah I'm pretty sure they have it

caleb.macdonaldblack01:03:00

Off the top of your head, do you know how I'd do this: extract(EPOCH FROM timestamp) in honeysql or honeysql-postgres?

caleb.macdonaldblack01:03:33

I know I can do (sql/call :extract ???)

seancorfield01:03:49

Not sure, sorry.

caleb.macdonaldblack01:03:05

Okay all good. I might be able to extend it

myguidingstar04:03:14

@caleb.macdonaldblack does alias work for you, ie "SELECT ... AS trunc_year, ... GROUP BY ..., trunc_year"?

caleb.macdonaldblack04:03:46

Yea that worked for me

caleb.macdonaldblack04:03:14

It duplicates that part in the sql query but I think that's fine

caleb.macdonaldblack04:03:52

actually sorry. Perhaps I misunderstood your question. it's not an alias

caleb.macdonaldblack04:03:16

SELECT ..., trunc_year, ... GROUP BY ..., trunc_year

caleb.macdonaldblack04:03:40

More like that. But trunc_year is not placed in the exactly like trunc_year

caleb.macdonaldblack04:03:51

in the sql. It expands out

myguidingstar04:03:26

I meant "AS truct_year"

caleb.macdonaldblack04:03:54

Yeah I can alias as well.

caleb.macdonaldblack04:03:10

Generated SQL: (1000 * date_part('epoch', date_trunc('year', to_timestamp((unix_timestamp_ms / 1000))))) AS unix_timestamp_ms

caleb.macdonaldblack04:03:33

(let [trunc-unix-timestamp-ms (->> (sql/call :/ :unix-timestamp-ms (sql/raw 1000))
                                     (sql/call :to_timestamp)
                                     (sql/call :date_trunc (sql/raw (str "'" (sanitize-aggregate aggregate) "'")))
                                     (sql/call :date_part (sql/raw "'epoch'"))
                                     (sql/call :* (sql/raw 1000)))]

caleb.macdonaldblack04:03:24

(-> (select [trunc-unix-timestamp-ms :unix-timestamp-ms])

myguidingstar04:03:52

hmm, that honeysql code doesn't look pretty to my eyes 😞

caleb.macdonaldblack04:03:56

(group trunc-unix-timestamp-ms)

caleb.macdonaldblack04:03:29

I'm definitely open to feedback if you have any 😛

caleb.macdonaldblack04:03:22

I don't like how I've done used sql/raw. But strings didn't work for me

myguidingstar04:03:50

I'm not a honeysql user at all

caleb.macdonaldblack04:03:35

Yeah I'm briefly looking through the docs now.

myguidingstar04:03:13

if you use walkable, for your case you'll define a "pseudo-column" :some-table/trunc-year

myguidingstar04:03:37

then you can use that column everywhere, including group-by

caleb.macdonaldblack04:03:44

That's interesting.

{:pseudo-column {:trunc_unix_timestamp_ms 
               [:* 1000
                [:date_part "epoch"
                 [:date_trunc 'aggregate
                  [:to_timestamp
                   [:/ :time-series/unix-timestamp-ms 1000]]]]]}}

myguidingstar04:03:31

you need :SOMENAMESPACE/trunc_unix_timestamp_ms

myguidingstar04:03:13

keywords without namespaced are understood as sql functions

myguidingstar04:03:53

:pseudo-columns instead of :pseudo-column

myguidingstar04:03:57

the doc for group-by is not there, please see the example here for now https://github.com/walkable-server/walkable/issues/124

myguidingstar04:03:53

:date_part, :date_trunc functions are not imported by default, but you can support them just by calling walkable.sql-query-builder.expressions/import-functions like this https://github.com/walkable-server/walkable/blob/master/src/walkable/sql_query_builder/impl/postgres.cljc#L21

myguidingstar05:03:59

I guess you don't want to use walkable variables there, but defining three pseudo columns instead

(defn timestamp-part [aggregate]
  [:* 1000
   [:date_part "epoch"
    [:date_trunc aggregate
     [:to_timestamp
      [:/ :time-series/unix-timestamp-ms 1000]]]]])

{:pseudo-columns
 {:time-series/trunc-by-year  (timestamp-part "year")
  :time-series/trunc-by-month (timestamp-part "year")
  :time-series/trunc-by-day   (timestamp-part "year")}}

caleb.macdonaldblack04:03:24

or maybe not :aggregators'

myguidingstar04:03:44

what is the symbol aggregate there?

caleb.macdonaldblack04:03:18

#{"day" "month" "year"}

myguidingstar04:03:50

a "variable", right?

caleb.macdonaldblack04:03:58

perhaps there is a different way to pass in variables?

caleb.macdonaldblack04:03:50

oh I would need to quote it

myguidingstar04:03:17

yeah, "variable" is its own term in walkable

amarjeet18:03:31

Hi, I am using clojure.java.jdbc with postgresql. At the time of table creation, if I provide [:age :int :default 18] then jdbc throws error (`java.lang.IllegalArgumentException: column spec is not a sequence of keywords / strings`), but it accepts "18", which is logically incorrect. How to handle this?

amarjeet18:03:55

The schema shows the int annotation though - 18:::INT under column_default

seancorfield18:03:18

DDL is notoriously database-specific so clojure.java.jdbc deliberately doesn't try to offer much in that area. I recommend you look at honeysql and, since you are using PostgreSQL, honeysql-postgres to build richer DDL.

seancorfield18:03:06

That said, accepting numbers in the column spec is a reasonable enhancement so I'll create a JIRA issue for that...