أنشئ أول عرض مادي

CREATE TABLE uk_price_paid_by_town
(
    town       LowCardinality(String),
    date       Date,
    price      UInt32,
    type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (town, date);

SHOW CREATE TABLE uk_price_paid_by_town;

CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
TO uk_price_paid_by_town
AS SELECT
    town,
    date,
    price,
    type
FROM uk_price_paid;

INSERT INTO uk_price_paid_by_town
SELECT
    town,
    date,
    price,
    type
FROM uk_price_paid;

SELECT
    'uk_price_paid' AS table,
    count() AS rows
FROM uk_price_paid
UNION ALL
SELECT
    'uk_price_paid_by_town' AS table,
    count() AS rows
FROM uk_price_paid_by_town;

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

SELECT
    toYear(date) AS year,
    round(avg(price)) AS avg_price,
    count() AS sales
FROM uk_price_paid_by_town
WHERE town = 'LONDON'
GROUP BY year
ORDER BY year DESC;

SHOW TABLES;

SELECT
    table,
    count() AS parts,
    sum(rows) AS total_rows,
    formatReadableSize(sum(bytes_on_disk)) AS compressed_size
FROM system.parts
WHERE table IN ('uk_price_paid', 'uk_price_paid_by_town')
  AND active = true
GROUP BY table;