2016-08-11 11:01 GMT+02:00 R Smith <[email protected]>:
> Woops, I think I had it right the first time then re-thought it wrongly :)
>
>
> Your SQL would then look like this:
>
>
> CREATE TABLE T (Grp INT);
>
> INSERT INTO T VALUES (5),(21),(27),(28),(22),(39),(
> 44),(45),(46),(51),(88);
>
> WITH TC(Tot) AS (
> SELECT CAST(COUNT(*) AS REAL) FROM T
> ), SL(Slice) AS (
> SELECT CAST((T.Grp / 10) AS INT) * 10
> FROM T
> )
> SELECT Slice||' - '||(Slice+9) AS Slice, COUNT(*) AS Cnt, ROUND(COUNT(*) /
> Tot * 100,2) AS Perc
> FROM SL, TC
> GROUP BY Slice
>
>
> -- Slice | Cnt | Perc
> -- --------- | --- | ---------
> -- 0 - 9 | 1 | 9.09
> -- 20 - 29 | 4 | 36.36
> -- 30 - 39 | 1 | 9.09
> -- 40 - 49 | 3 | 27.27
> -- 50 - 59 | 1 | 9.09
> -- 80 - 89 | 1 | 9.09
That brought me to the right place. A little change because I want the
first slice to go from 0 to 10 including. Also it is now very simple to
change the slice length. An example for slice length = 5:
"86 - 90" "55.7"
"81 - 85" "27.87"
"91 - 95" "12.82"
"76 - 80" "2.69"
"71 - 75" "0.28"
"66 - 70" "0.24"
"61 - 65" "0.23"
"96 - 100" "0.07"
"56 - 60" "0.05"
"26 - 30" "0.02"
"11 - 15" "0.01"
"21 - 25" "0.01"
"41 - 45" "0.01"
"51 - 55" "0.01"
Not bad more as 95% more as 80% idle. ;-)
The SQL code:
WITH
STAT_DATE(likeString) AS (
-- Today
-- SELECT CURRENT_DATE
-- This month
SELECT substr(CURRENT_DATE, 1, 8) || "%"
-- This year
-- SELECT substr(CURRENT_DATE, 1, 5) || "%"
-- All data
-- SELECT "%"
),
STAT_INFO(onePercent, likeString, sliceLength) AS (
SELECT COUNT(*) / 100.0
, likeString
, 5
FROM vmstat
, STAT_DATE
WHERE date LIKE likeString
),
STAT_SLICES(sliceStart) AS (
SELECT CAST(((idleTime - 1) / sliceLength) AS INT) * sliceLength
FROM vmstat
, STAT_INFO
WHERE date LIKE likeString
)
SELECT CASE WHEN (sliceStart = 0) THEN
sliceStart
ELSE
(sliceStart + 1)
END ||' - ' || (sliceStart + sliceLength) AS Slice
, ROUND(COUNT(*) / onePercent, 2) AS Percentage
FROM STAT_SLICES
, STAT_INFO
GROUP BY Slice
ORDER BY Percentage DESC
I am not easily satisfied. ;-)
As title for the first column I now have Slice, but I would prefer
something like:
Slice (2016-08-%)
So it is easily to know for which period the info is.
I tried:
AS Slice || ' (' || likeString || ')'
but that does not work.
Is what I want possible?
--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
