Clickmap statistieken vernieuwen

-- ============================================================
-- NL_clickmap_stats aanmaken (uitvoeren in SSMS syndiNL)
-- Bron: schoonmakendnederland (b_exclude=0, 6.09M panden)
-- Geschatte tijd: 5-10 minuten
-- ============================================================

-- Stap 1: EPC per gemeente (per verblijfsobject)
IF OBJECT_ID('tempdb..#epc_gem','U') IS NOT NULL DROP TABLE #epc_gem;
SELECT a.gemeente, ep.energieklasse, COUNT(*) AS cnt
INTO #epc_gem
FROM dbo.NL_adres_buildingId a
INNER JOIN dbo.epc ep ON ep.bagverblijfsobjectid = a.verblijfsobject_id
WHERE a.gemeente IS NOT NULL AND a.gemeente <> '' AND ep.energieklasse IS NOT NULL
GROUP BY a.gemeente, ep.energieklasse;

-- Stap 2: Adressen per gemeente
IF OBJECT_ID('tempdb..#adr_gem','U') IS NOT NULL DROP TABLE #adr_gem;
SELECT gemeente, COUNT(*) AS adressen
INTO #adr_gem
FROM dbo.NL_adres_buildingId
WHERE gemeente IS NOT NULL AND gemeente <> ''
GROUP BY gemeente;

-- Stap 3: Hoofd-aggregatie op schoonmakendnederland (kan 5-10 min duren)
IF OBJECT_ID('dbo.NL_clickmap_stats','U') IS NOT NULL DROP TABLE dbo.NL_clickmap_stats;

SELECT
    gemeente,
    MIN(provincie)                                                                         AS provincie,
    COUNT(*)                                                                               AS panden,
    CAST(ROUND(AVG(CAST(bouwjaar_bag      AS FLOAT)), 0) AS INT)                           AS gem_bouwjaar,
    CAST(ROUND(AVG(CAST(bouwlagen_berekend AS FLOAT)), 2) AS DECIMAL(5,2))                 AS gem_bouwlagen,
    CAST(ROUND(AVG(CAST(CASE WHEN f_woon = 1 THEN bouwlagen_berekend END AS FLOAT)), 2)
         AS DECIMAL(5,2))                                                                  AS gem_bouwlagen_woon,
    CAST(ROUND(AVG(CAST(CASE WHEN f_woon = 0 THEN bouwlagen_berekend END AS FLOAT)), 2)
         AS DECIMAL(5,2))                                                                  AS gem_bouwlagen_nietwoon,
    CAST(ROUND(AVG(CAST(hoogte_dakrand AS FLOAT)), 1) AS DECIMAL(6,1))                    AS gem_hoogte,
    SUM(CASE WHEN bouwlagen_berekend = 1              THEN 1 ELSE 0 END)                   AS bl_1,
    SUM(CASE WHEN bouwlagen_berekend = 2              THEN 1 ELSE 0 END)                   AS bl_2,
    SUM(CASE WHEN bouwlagen_berekend = 3              THEN 1 ELSE 0 END)                   AS bl_3,
    SUM(CASE WHEN bouwlagen_berekend = 4              THEN 1 ELSE 0 END)                   AS bl_4,
    SUM(CASE WHEN bouwlagen_berekend = 5              THEN 1 ELSE 0 END)                   AS bl_5,
    SUM(CASE WHEN bouwlagen_berekend BETWEEN 6 AND 10 THEN 1 ELSE 0 END)                   AS bl_6_10,
    SUM(CASE WHEN bouwlagen_berekend > 10             THEN 1 ELSE 0 END)                   AS bl_11plus,
    SUM(CASE WHEN bouwlagen_berekend IS NULL          THEN 1 ELSE 0 END)                   AS bl_onbekend,
    SUM(CAST(dak_schuin AS INT))                                                            AS dak_schuin,
    SUM(CAST(dak_plat   AS INT))                                                            AS dak_plat,
    SUM(CASE WHEN bouwjaar_bag < 1900                 THEN 1 ELSE 0 END)                   AS j_voor_1900,
    SUM(CASE WHEN bouwjaar_bag BETWEEN 1900 AND 1944  THEN 1 ELSE 0 END)                   AS j_1900_1944,
    SUM(CASE WHEN bouwjaar_bag BETWEEN 1945 AND 1974  THEN 1 ELSE 0 END)                   AS j_1945_1974,
    SUM(CASE WHEN bouwjaar_bag BETWEEN 1975 AND 1999  THEN 1 ELSE 0 END)                   AS j_1975_1999,
    SUM(CASE WHEN bouwjaar_bag BETWEEN 2000 AND 2009  THEN 1 ELSE 0 END)                   AS j_2000_2009,
    SUM(CASE WHEN bouwjaar_bag >= 2010                THEN 1 ELSE 0 END)                   AS j_2010plus,
    SUM(CASE WHEN bouwlagen_berekend >= 10            THEN 1 ELSE 0 END)                   AS hoogbouw_10plus,
    SUM(CASE WHEN bouwlagen_berekend >= 20            THEN 1 ELSE 0 END)                   AS hoogbouw_20plus,
    0 AS epc_aplus, 0 AS epc_a, 0 AS epc_b,
    0 AS epc_c,     0 AS epc_d, 0 AS epc_e,
    0 AS epc_fg,    0 AS adressen
INTO dbo.NL_clickmap_stats
FROM dbo.schoonmakendnederland
WHERE b_exclude = 0
  AND gemeente IS NOT NULL AND gemeente <> ''
GROUP BY gemeente;

CREATE INDEX IX_clickmap_gemeente ON dbo.NL_clickmap_stats (gemeente);

-- Stap 4: EPC en adressen invullen
UPDATE s SET
    epc_aplus = ISNULL(ep.epc_aplus,0),
    epc_a     = ISNULL(ep.epc_a,0),
    epc_b     = ISNULL(ep.epc_b,0),
    epc_c     = ISNULL(ep.epc_c,0),
    epc_d     = ISNULL(ep.epc_d,0),
    epc_e     = ISNULL(ep.epc_e,0),
    epc_fg    = ISNULL(ep.epc_fg,0),
    adressen  = ISNULL(ad.adressen,0)
FROM dbo.NL_clickmap_stats s
LEFT JOIN (
    SELECT gemeente,
        SUM(CASE WHEN energieklasse IN ('A++++','A+++','A++','A+') THEN cnt ELSE 0 END) AS epc_aplus,
        SUM(CASE WHEN energieklasse = 'A'       THEN cnt ELSE 0 END) AS epc_a,
        SUM(CASE WHEN energieklasse = 'B'       THEN cnt ELSE 0 END) AS epc_b,
        SUM(CASE WHEN energieklasse = 'C'       THEN cnt ELSE 0 END) AS epc_c,
        SUM(CASE WHEN energieklasse = 'D'       THEN cnt ELSE 0 END) AS epc_d,
        SUM(CASE WHEN energieklasse = 'E'       THEN cnt ELSE 0 END) AS epc_e,
        SUM(CASE WHEN energieklasse IN ('F','G') THEN cnt ELSE 0 END) AS epc_fg
    FROM #epc_gem GROUP BY gemeente
) ep ON ep.gemeente = s.gemeente
LEFT JOIN #adr_gem ad ON ad.gemeente = s.gemeente;

DROP TABLE #epc_gem, #adr_gem;

-- Verificatie
SELECT COUNT(*) AS gemeenten, SUM(panden) AS panden_totaal,
       ROUND(AVG(CAST(gem_bouwlagen         AS FLOAT)),2) AS nl_gem_bouwlagen,
       ROUND(AVG(CAST(gem_bouwlagen_woon    AS FLOAT)),2) AS nl_gem_bouwlagen_woon,
       ROUND(AVG(CAST(gem_bouwlagen_nietwoon AS FLOAT)),2) AS nl_gem_bouwlagen_nietwoon,
       ROUND(AVG(CAST(gem_hoogte            AS FLOAT)),1) AS nl_gem_hoogte
FROM dbo.NL_clickmap_stats;
SELECT TOP 10 gemeente, provincie, panden, gem_bouwjaar, gem_bouwlagen,
              gem_bouwlagen_woon, gem_bouwlagen_nietwoon, gem_hoogte
FROM dbo.NL_clickmap_stats ORDER BY panden DESC;