Kā saskaitīt kolonnas laikrindu tabulās ar atkārtotiem pasūtījuma numuriem

Laikrindu datu apkopošana ar SQL atkārtotu pasūtījumu numuriem

Šis risinājums izmanto SQL, lai izveidotu modulāru vaicājumu, kas apstrādā neunikālus pasūtījumu numurus ar laikrindu apkopošanu.

-- 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;

Procesuālās SQL izmantošana ar PL/pgSQL pielāgotai apkopošanai

Šī pieeja izmanto PL/pgSQL programmā PostgreSQL dinamiskai un iteratīvai apstrādei pa rindām.

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 aizmugursistēmas risinājums ar Node.js un SQL integrāciju

Šis aizmugursistēmas risinājums izmanto Node.js, lai dinamiski apstrādātu SQL datus, iekļaujot kļūdu apstrādi un modulāras 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();

Bieži uzdotie jautājumi par SQL laikrindu apkopošanu

1. Kāds ir mērķis LEAD() un LAG() SQL?
2. The LEAD() funkcija ienes vērtību no nākamās rindas, while LAG() izgūst vērtību no iepriekšējās rindas. Tos izmanto, lai identificētu pārejas vai izmaiņas rindās, piemēram, izsekotu izmaiņām pasūtījuma_id.
3. Kā lietot GROUP BY laikrindu datiem?
4. Jūs varat izmantot GROUP BY lai apkopotu rindas, pamatojoties uz kopīgu kolonnu, piemēram pasūtījuma_id, vienlaikus piemērojot tādas apkopotas funkcijas kā SUM() vai MAX() apvienot vērtības visā grupā.
5. Kādi ir ieguvumi no WITH Kopējās tabulu izteiksmes (CTE)?
6. CTEs vienkāršo vaicājumus, ļaujot definēt pagaidu rezultātu kopas, kuras ir viegli lasīt un izmantot atkārtoti. Piemēram, CTE pirms apkopošanas var identificēt grupas sākumu un beigas.
7. Vai es varu izmantot rekursīvos vaicājumus laikrindu apkopošanai?
8. Jā! Rekursīvie vaicājumi ir noderīgi, lai saistītu datu rindas, kas ir atkarīgas viena no otras. Piemēram, varat “ķēdēt” rindas ar pārklāšanās laikiem, lai iegūtu sarežģītākus apkopojumus.
9. Kā nodrošināt precizitāti, strādājot ar laika diapazonu pārklāšanos?
10. Lai izvairītos no dubultas uzskaites, vaicājumā izmantojiet nosacījumu loģiku, piemēram, filtrējiet vai iestatiet robežas. Apvienojot CASE paziņojumi ar loga funkcijām var palīdzēt pārvaldīt šīs pārklāšanās.

Nobeigums ar SQL apkopošanas ieskatiem

Izpratne par to, kā rīkoties atkārtoti pasūtījuma_id vērtības laikrindu datos ir ļoti svarīgas precīzai datu apstrādei. Šajā rakstā ir izceltas dažādas metodes, piemēram, CTE un logu funkcijas, lai vienkāršotu sarežģītus vaicājumus un nodrošinātu jēgpilnus rezultātus. Šīs stratēģijas ir būtiskas scenārijiem, kas saistīti ar pārklāšanos vai sadrumstalotiem pasūtījumiem.

Neatkarīgi no tā, vai veidojat ražošanas informācijas paneli vai analizējat laika ziņā jutīgus datus, šīs SQL prasmes uzlabos jūsu iespējas. Apvienojot modulāro vaicājumu dizainu ar uzlabotām funkcijām, jūsu risinājumi ir gan efektīvi, gan apkopjami. Izmantojiet šīs metodes savos projektos, lai pilnībā izmantotu laikrindu datu analīzes potenciālu! 😊