Cipta Carta Histogram Dengan MySQL – Tutorial

Untuk membuat pertanyaan statistik untuk membina carta histogram dengan MySQL, anda boleh menggunakan fungsi COUNT() bersama-sama dengan GROUP BY untuk mengira kejadian nilai dalam julat atau kategori tertentu yang dicipta oleh kekangan kumpulan.

Terutama untuk data siri masa terdapat banyak kes penggunaan untuk histogram seperti memantau bilangan pengguna yang didaftarkan pada selang harian dalam tempoh 30 hari yang lalu. Kami menggunakan pertanyaan ini dalam bahagian belakang pentadbiran kami untuk memantau beberapa KPI penting.

Malangnya pangkalan data SQL tidak pernah mempunyai sokongan asli untuk mewakili histogram, tetapi ia adalah antara carta yang paling banyak digunakan untuk menjejak metrik dari semua jenis.

Dalam artikel ini, saya akan menunjukkan kepada anda cara membuat pertanyaan untuk tujuan ini dan mengatasi beberapa batasan. Katakan anda mempunyai jadual bernama jualan dengan lajur bernama sale_date yang mengandungi tarikh dan masa setiap jualan. Anda ingin membuat carta histogram yang menunjukkan taburan jualan sepanjang selang bulanan.

SELECT 
    DATE_FORMAT(sale_date, '%Y-%m') AS sales_month,
    COUNT(*) AS count
FROM 
    sales
GROUP BY 
    DATE_FORMAT(sale_date, '%Y-%m');

n pertanyaan ini:

• DATE_FORMAT(sale_date, '%Y-%m') memformat lajur sale_date ke dalam format tahun-bulan (cth., "2024-01" untuk Januari 2024).
• COUNT(*) mengira bilangan kejadian jualan dalam setiap bulan.
• FROM sales menentukan jadual untuk mendapatkan semula data.
• KUMPULAN MENGIKUT TARIKH_FORMAT(tarikh_jualan, '%Y-%m') mengumpulkan data jualan ke dalam selang bulanan berdasarkan tarikh jualan yang diformatkan.

Pertanyaan ini akan memberi anda kiraan kejadian jualan dalam setiap selang bulanan, membolehkan anda membuat carta histogram untuk menggambarkan taburan jualan dari semasa ke semasa.

Cara mengisi jurang dalam pertanyaan histogram
Menjalankan pertanyaan ini, anda mungkin akan melihat beberapa bulan yang hilang dalam set hasil, mungkin kerana pada bulan tertentu anda tidak mempunyai jualan, jadi fungsi GROUP BY tidak dapat menjana data untuk selang ini.

Bagaimanakah pertanyaan boleh dilaraskan untuk mengisi jurang ini dengan nilai sifar lalai? Jika tidak, histogram kekal tidak lengkap.

Untuk mengisi jurang dalam set hasil dan memastikan semua bulan diwakili, walaupun tiada jualan dalam bulan tertentu, anda boleh menjana bulan secara dinamik untuk selang kalendar yang dipilih menggunakan ungkapan jadual biasa (CTE) rekursif.

Ia menjana urutan tarikh meliputi julat masa yang diingini. Kemudian, anda boleh MENINGGALKAN SERTAI jujukan tarikh ini dengan data jualan anda untuk memasukkan semua bulan dalam set hasil.

WITH RECURSIVE DateRange AS (
    SELECT 
        DATE_FORMAT(DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 11 MONTH), '%Y-%m-01') AS min_date,
        DATE_FORMAT(NOW(), '%Y-%m-01') AS max_date
    UNION ALL
    SELECT 
        DATE_FORMAT(DATE_ADD(min_date, INTERVAL 1 MONTH), '%Y-%m-01'),
        max_date
    FROM 
        DateRange
    WHERE 
        DATE_ADD(min_date, INTERVAL 1 MONTH) <= max_date
)
SELECT 
    DATE_FORMAT(DateRange.min_date, '%Y-%m') AS sales_month,
    COUNT(sales.sale_date) AS count
FROM 
    DateRange
LEFT JOIN 
    sales ON DATE_FORMAT(DateRange.min_date, '%Y-%m') = DATE_FORMAT(sales.sale_date, '%Y-%m')
GROUP BY 
    sales_month
ORDER BY 
    sales_month;

CTE Julat Tarikh (ungkapan jadual biasa) secara rekursif menjana jujukan bulan meliputi julat masa antara tarikh jualan minimum dan maksimum dalam jadual jualan anda.

Pertanyaan ini menjana bulan secara dinamik untuk selang kalendar yang dipilih berdasarkan tarikh jualan minimum dan maksimum dalam jadual jualan anda, memastikan semua bulan diwakili dalam set hasil.

Isi jurang dengan kod

Penyelesaian SQL mungkin agak kurang selesa untuk pembangun, dan juga keperluan untuk lebih banyak penyesuaian mungkin membawa kepada pilihan penyelesaian berasaskan kod.

Dalam kes ini, anda boleh mencapai hasil yang sama dalam tiga langkah mudah:

• Buat tatasusunan dengan selang masa minat anda;
• Dapatkan hasil daripada pertanyaan GROUP BY;
• Gabungkan mereka.

Berikut ialah coretan kod menggunakan Laravel dan pustaka Carbon:

$dates = [];

// Create the array with the time interval of your interests
for(
    $day = now()->subDays(31);
    $day->startOfDay()->lte(now());
    $day->addDay()
) {
    $dates[] = [
        'day' => $day->format('Y-m-d'),
        'total' => 0,
    ];
}

// Get the result from the GROUP BY query
$sales = $product->sales()->select(DB::raw('DATE(sale_at) as day, CAST(SUM(qty) AS UNSIGNED) as total'))
    ->where('sale_at', '>=', now()->subDays(31))
    ->groupBy('day')
    ->get();

// Merge them
return array_map(function ($date) use ($sales) {
    foreach ($sales as $sale) {
        if ($date['day'] === $sale['day']) {
            return $sale;
        }
    }
}, $dates);

Jika anda berminat untuk membaca lebih banyak siaran tentang pangkalan data, anda boleh menyemak artikel di bawah:

• Cara menskala pangkalan data SQL
• Bagaimana untuk menduplikasi jadual MySQL
• Diselesaikan - Pelanggaran kekangan integriti
• Jimatkan 1.2 juta pertanyaan setiap hari dengan Laravel Eager Loading
• Cara mempercepatkan prestasi aplikasi dengan pertanyaan SQL pintar
• Jadikan aplikasi anda berskala dengan mengoptimumkan prestasi ORM
• Diselesaikan – tamat masa menunggu kunci MySQL melebihi menggunakan baris gilir dan kerja Laravel

Pantau aplikasi PHP anda secara percuma

Inspektor ialah alat Pemantauan Pelaksanaan Kod yang direka khusus untuk pembangun perisian. Anda tidak perlu memasang apa-apa pada infrastruktur awan, cuma pasang pakej Laravel dan anda sudah bersedia untuk pergi.

Inspektor sangat mudah digunakan dan memerlukan konfigurasi sifar.

Jika anda sedang mencari pemantauan HTTP, cerapan pertanyaan dan keupayaan untuk memajukan makluman dan pemberitahuan ke dalam persekitaran pemesejan pilihan anda cuba Inspektor secara percuma. [Daftar akaun anda+(https://app.inspector.dev/register).

Atau ketahui lebih lanjut di tapak web: https://inspector.dev

Atas ialah kandungan terperinci Cipta Carta Histogram Dengan MySQL – Tutorial. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!