I’ve been blogging long enough to observe that your awesome posts are often ignored while your little “one-offs” sometimes strike hit gold. This is particularly true for “code example” posts. When I’m trolling for code — the geek equivalent of trolling for babes — I don’t want to read about the author’s programming philosophy or what the hell he thinks about the idiot in the White House. Just show me the code and shut up!
So, taking my own advice, the following is an example of a SQLSever Common Table Expression (CTE) query.
WITH cte1
AS (SELECT c.obj_id AS obj_id,
a.created AS creation_date,
Datediff(DAY, a.created, Getdate()) AS day_cnt
FROM [HIST].[dbo].[History] a
JOIN [HIST].[dbo].[HistorySummary] b
ON a.rowid = b.rowid
JOIN [HIST].[dbo].[ObjectNames] c
ON c.obj_name = b.obj_name),
cte2
AS (SELECT cte1.obj_id AS obj_id,
CASE
WHEN cte1.day_cnt <= 30 THEN 1
ELSE 0
END AS d0,
CASE
WHEN ( cte1.day_cnt > 30 )
AND ( cte1.day_cnt <= 60 ) THEN 1
ELSE 0
END AS d1,
CASE
WHEN ( cte1.day_cnt > 60 )
AND ( cte1.day_cnt <= 90 ) THEN 1
ELSE 0
END AS d2,
CASE
WHEN ( cte1.day_cnt > 90 ) THEN 1
ELSE 0
END AS d3
FROM cte1)
SELECT obj_id,
SUM(d0) AS OneMonth,
SUM(d1) AS TwoMonths,
SUM(d2) AS ThreeMonths,
SUM(d3) AS Overdue
FROM cte2
GROUP BY obj_id
CTE queries essentially create temporary virtual tables during query execution. They are similar to nested SQL queries but are easier to code, more general, (see recursive CTE queries), and often perform better than their convoluted equivalents. This example creates two virtual tables cte1 and cte2 that are then used to compute a quick histogram.
You can call CTE queries with the J ODBC interface. The following assumes a SQLServer ODBC connection dsn history.
NB. odbc interface
require 'dd'
NB. read CTE query
HistoryAgeSQL=. read 'c:/temp/HistoryAge.sql'
NB. connect sqlserver database
ch =. ddcon 'dsn=history'
NB. select with CTE query
sh =. HistoryAgeSQL ddsel ch
NB. fetch results
data=. ddfet sh,_1
As a final note it’s worth comparing the SQL CTE histogram code with J equivalents. The two following J verbs taken from the J wiki, (here and here), compute histograms.
NB. computes histograms uses right open intervals
histogram=:<:@(#/.~)@(i.@#@[ , I.)
NB. variation on (histogram) uses left open intervals
histogram2=:<:@(#/.~)@(i.@>:@#@[ , |.@[ (#@[ - I.) ])
They scale to tens of millions of data points; returning results in a few seconds on my laptop. The SQL CTE, shown above, takes about three seconds running on 180,000 row tables on my employer’s full warp servers. If I could convince the masses to adopt languages like J a large part of my job would disappear. Fortunately, the world is hostile to terse elegance!
Recent Comments