tldr:
what is the best template to get to a count of the problematic modules in this attribute:
using the example config from the documentation on SolarEdge SQL sensors, I have this entity that has too many chars for its state (which is effectively duplicating the attribute it sets for problematic modules:
State 1.1.1 (36.3%), 1.1.10 (0.0%), 1.1.11 (65.5%), 1.1.12 (17.7%), 1.1.15 (62.3%), 1.1.16 (43.4%), 1.1.17 (0.0%), 1.1.18 (8.3%), 1.1.19 (29.2%), 1.1.2 (42.3%), 1.1.3 (0.0%), 1.1.4 (0.0%), 1.1.5 (0.0%), 1.1.6 (22.0%), 1.1.7 (91.3%), 1.1.8 (0.0%), 1.1.9 (2.8%) for sensor.solaredge_lage_productie_panelen_z is longer than 255, falling back to unknown
Now this is hopefully not the case too often… but, as we’ve been covered with snow for the last week or so now, I’d love to use another state for the entity, and can do so I think in the options for the entity:
now what should I do to get the number of the problematic_modules in the state?
Or, would there be another more useful method/option to use for these entities (I have several planes of modules)
{{this.attributes.problematic_modules.split(',')|count}} would be simplest?
however, doing so still renders unknown, even though testing it in the dev tools shows the correct output
which returns this to me for the query, providing the attributes in 2 versions (JSON for easy templating) and a failing count:
SELECT * FROM (
WITH RelevantTimeRange AS (
SELECT
strftime('%s', date('now', 'localtime', 'start of day', '-7 days')) AS period_start_ts,
strftime('%s', date('now', 'localtime', 'start of day')) AS period_end_ts
),
ModuleProduction AS (
SELECT
sm.name,
SUM(s.state) AS total_production
FROM statistics s
JOIN statistics_meta sm ON s.metadata_id = sm.id
CROSS JOIN RelevantTimeRange rt
WHERE
sm.source = 'solaredge'
AND sm.name LIKE '% 1.1.%'
AND s.start_ts >= rt.period_start_ts
AND s.start_ts < rt.period_end_ts
AND s.state IS NOT NULL
GROUP BY
sm.name
),
AverageProduction AS (
SELECT
AVG(mp.total_production) AS average_total_production
FROM ModuleProduction mp
WHERE total_production > 0
)
SELECT
COUNT(*) AS failing_count,
-- Human-readable string, sorted ascending (worst first)
IFNULL(
GROUP_CONCAT(
mp.name || ' (' ||
printf("%.1f", (mp.total_production * 100.0 / ap.average_total_production)) ||
'%)',
', '
ORDER BY mp.total_production * 100.0 / ap.average_total_production ASC
),
''
) AS problematic_modules,
-- Structured JSON, sorted ascending (worst first)
json_group_array(
json_object(
'name', mp.name,
'percent', ROUND(mp.total_production * 100.0 / ap.average_total_production, 1)
)
ORDER BY mp.total_production * 100.0 / ap.average_total_production ASC
) AS problematic_modules_json
FROM
ModuleProduction mp
CROSS JOIN AverageProduction ap
WHERE
mp.total_production < (0.95 * ap.average_total_production)
AND ap.average_total_production IS NOT NULL
AND ap.average_total_production > 0
) AS result;
and I do believe this works like I had hoped to.
the count is obvious, the string attribute can be used eg in a Markdown, and the json can be the base for more in-depth templating
