On 2017/03/08 5:35 PM, Dominique Devienne wrote:
Now I only need a CTE to dynamically generate the ranges,
Well, that seems like fun!
If I may...
-- CTEs
-- Units Table (B, KB, MB, etc.)
WITH UT(id, uStr, byteDiv) AS (
SELECT 0, 'B', 1
UNION ALL
SELECT UT.id+1,
CASE UT.id WHEN 0 THEN 'KB' WHEN 1 THEN 'MB' WHEN 2 THEN
'GB' WHEN 3 THEN 'TB' WHEN 4 THEN 'PB' END,
UT.byteDiv * 1024
FROM UT
WHERE UT.id < 5
-- Range Table (define lows/highs)
), RT(id, low, high) AS (
SELECT 0, 0, 1024
UNION ALL
SELECT RT.id+1, RT.high, RT.high*8
FROM RT
WHERE RT.id < 16
-- Conversion Values (Match low/high Ranges to Units)
), CV(rid, low, high, luStr, luDiv, huStr, huDiv) AS (
SELECT RT.id, RT.low, RT.high, ULo.uStr, ULo.byteDiv, UHi.uStr,
UHi.byteDiv
FROM RT
LEFT JOIN UT AS ULo ON (ULo.byteDiv <= RT.low AND
(ULo.byteDiv*1024) > RT.low ) OR (ULo.id = 0 AND RT.id = 0)
LEFT JOIN UT AS UHi ON (UHi.byteDiv <= RT.high AND
(UHi.byteDiv*1024) > RT.high)
WHERE UHi.id IS NOT NULL
-- Range Table with Name vs. limits definitions
), range(name, low, high) AS (
SELECT '['|| (CV.low / CV.luDiv) || CV.luStr ||' - '|| (CV.high /
CV.huDiv) || CV.huStr ||']', CV.low, CV.high
FROM CV
)
SELECT *
FROM range
;
-- name | low | high
-- ---------------- | --------------------- | ---------------------
-- [0B - 1KB] | 0 | 1024
-- [1KB - 8KB] | 1024 | 8192
-- [8KB - 64KB] | 8192 | 65536
-- [64KB - 512KB] | 65536 | 524288
-- [512KB - 4MB] | 524288 | 4194304
-- [4MB - 32MB] | 4194304 | 33554432
-- [32MB - 256MB] | 33554432 | 268435456
-- [256MB - 2GB] | 268435456 | 2147483648
-- [2GB - 16GB] | 2147483648 | 17179869184
-- [16GB - 128GB] | 17179869184 | 137438953472
-- [128GB - 1TB] | 137438953472 | 1099511627776
-- [1TB - 8TB] | 1099511627776 | 8796093022208
-- [8TB - 64TB] | 8796093022208 | 70368744177664
-- [64TB - 512TB] | 70368744177664 | 562949953421312
-- [512TB - 4PB] | 562949953421312 | 4503599627370496
-- [4PB - 32PB] | 4503599627370496 | 36028797018963968
-- [32PB - 256PB] | 36028797018963968 | 288230376151711744
(Hope the format survived e-mailing....)
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
