Home assistant helpers

usage_by_count.sql

SELECT COUNT(1) INTO @total FROM states;

WITH t AS (
  SELECT ROW_NUMBER() OVER (ORDER BY `count` DESC) AS row_num, entity_id, count(1) `count`, ROUND(100 * COUNT(1) / @total, 2) percent_of_total
  FROM `states` 
  GROUP BY entity_id
  ORDER BY 2 DESC
)
SELECT *, SUM(percent_of_total) OVER (ORDER BY `row_num`) AS cumulative
FROM t

usage_by_size.sql

SELECT SUM(LENGTH(IFNULL(attributes, 0))) INTO @total_size FROM states;

WITH t AS (
	SELECT ROW_NUMBER() OVER (ORDER BY `size` DESC) AS row_num, entity_id, count(1) `count`, SUM(LENGTH(IFNULL(attributes, 0))) size, ROUND(100 * SUM(LENGTH(IFNULL(attributes, 0))) / @total_size, 2) AS percent_of_total
	FROM `states` 
	GROUP BY entity_id
	ORDER BY size DESC
)
SELECT *, SUM(percent_of_total) OVER (ORDER BY `row_num`) AS cumulative
FROM t