Kaip pridėti stulpelius laiko eilučių lentelėse su pasikartojančiais užsakymų numeriais

Laiko eilučių duomenų kaupimas naudojant SQL kartotinių užsakymų numeriams

Šis sprendimas naudoja SQL, kad sukurtų modulinę užklausą, apdorojančią neunikalius užsakymų numerius su laiko eilučių agregavimu.

-- Define a Common Table Expression (CTE) to track transitions between order IDs
WITH order_transitions AS (
    SELECT
        *,
        LAG(order_id) OVER (ORDER BY start) AS prev_id,
        LEAD(order_id) OVER (ORDER BY start) AS next_id
    FROM production
)
-- Create a query to handle gaps and the first line issue
SELECT
    order_id,
    MIN(start) AS start,
    MAX(end) AS end,
    SUM(count) AS total_count
FROM (
    SELECT
        order_id,
        start,
        end,
        count,
        CASE
            WHEN prev_id != order_id OR prev_id IS  THEN ROW_NUMBER() OVER (ORDER BY start)
            ELSE 
        END AS grouping_flag
    FROM order_transitions
) t
GROUP BY order_id, grouping_flag
ORDER BY start;

Procedūrinio SQL naudojimas su PL/pgSQL tinkintam agregavimui

Šis metodas naudoja PL/pgSQL „PostgreSQL“ dinaminiam ir pasikartojančiam eilučių apdorojimui.

DO $$
DECLARE
    curr_order_id INTEGER;
    curr_start TIMESTAMP;
    curr_end TIMESTAMP;
    curr_count INTEGER;
BEGIN
    -- Create a temp table to hold results
    CREATE TEMP TABLE aggregated_data (
        order_id INTEGER,
        start TIMESTAMP,
        end TIMESTAMP,
        count INTEGER
    );
    -- Loop through each row in production
    FOR row IN SELECT * FROM production ORDER BY start LOOP
        IF curr_order_id IS DISTINCT FROM row.order_id THEN
            -- Insert previous aggregated row
            INSERT INTO aggregated_data VALUES (curr_order_id, curr_start, curr_end, curr_count);
            -- Reset for new group
            curr_order_id := row.order_id;
            curr_start := row.start;
            curr_end := row.end;
            curr_count := row.count;
        ELSE
            -- Aggregate within the same group
            curr_end := row.end;
            curr_count := curr_count + row.count;
        END IF;
    END LOOP;
END $$;

JavaScript Backend sprendimas su Node.js ir SQL integracija

Šis vidinis sprendimas naudoja Node.js, kad dinamiškai apdorotų SQL duomenis, įtraukdamas klaidų apdorojimą ir modulines funkcijas.

const { Client } = require('pg'); // PostgreSQL client
const aggregateData = async () => {
    const client = new Client({
        user: 'user',
        host: 'localhost',
        database: 'production_db',
        password: 'password',
        port: 5432
    });
    try {
        await client.connect();
        const query = `WITH lp AS (
            SELECT *, LEAD(order_id) OVER (ORDER BY start) AS next_id FROM production
        )
        SELECT order_id, MIN(start) AS start, MAX(end) AS end, SUM(count) AS count
        FROM lp
        GROUP BY order_id
        ORDER BY MIN(start);`;
        const result = await client.query(query);
        console.log(result.rows);
    } catch (err) {
        console.error('Error executing query:', err);
    } finally {
        await client.end();
    }
};
aggregateData();

Dažnai užduodami klausimai apie SQL laiko eilučių agregavimą

1. Koks tikslas LEAD() ir LAG() SQL?
2. The LEAD() funkcija paima reikšmę iš kitos eilutės, while LAG() nuskaito vertę iš ankstesnės eilutės. Jie naudojami eilučių perėjimams ar pakeitimams nustatyti, pvz., stebėti pokyčius order_id.
3. Kaip aš naudoju GROUP BY laiko eilučių duomenims?
4. Galite naudoti GROUP BY apibendrinti eilutes pagal bendrą stulpelį, pvz order_id, o taikant suvestines funkcijas, pvz SUM() arba MAX() derinti vertybes visoje grupėje.
5. Kokia nauda iš WITH Bendrosios lentelės išraiškos (CTE)?
6. CTE supaprastina užklausas, nes leidžia apibrėžti laikinus rezultatų rinkinius, kuriuos lengva skaityti ir pakartotinai naudoti. Pavyzdžiui, CTE gali nustatyti grupės pradžią ir pabaigą prieš sujungdamas.
7. Ar galiu naudoti rekursines užklausas laiko eilėms kaupti?
8. Taip! Rekursinės užklausos yra naudingos susiejant duomenų eilutes, kurios priklauso viena nuo kitos. Pavyzdžiui, galite „sujungti“ eilutes su persidengiančiais laikais, kad gautumėte sudėtingesnius agregatus.
9. Kaip užtikrinti tikslumą dirbant su persidengiančiais laiko intervalais?
10. Kad išvengtumėte dvigubo skaičiavimo, užklausoje naudokite sąlyginę logiką, pvz., filtruokite arba nustatykite ribas. Sujungimas CASE teiginiai su langų funkcijomis gali padėti valdyti šiuos sutapimus.

Baigimas su SQL agregavimo įžvalgomis

Supratimas, kaip elgtis pakartotinai order_id laiko eilučių duomenų reikšmės yra labai svarbios norint tiksliai apdoroti duomenis. Šiame straipsnyje akcentuojami įvairūs metodai, pvz., CTE ir langų funkcijos, siekiant supaprastinti sudėtingas užklausas ir užtikrinti reikšmingus rezultatus. Šios strategijos yra būtinos scenarijams, susijusiems su persidengiančiais arba suskaidytais užsakymais.

Nesvarbu, ar kuriate gamybos prietaisų skydelį, ar analizuojate laiko atžvilgiu svarbius duomenis, šie SQL įgūdžiai padidins jūsų galimybes. Sujungus modulinį užklausų dizainą su pažangiomis funkcijomis, jūsų sprendimai bus veiksmingi ir prižiūrimi. Taikykite šiuos metodus savo projektuose, kad išnaudotumėte visą laiko eilučių duomenų analizės potencialą! 😊