MySQL Queries

MySQL queries are queries written in the Compass module against a MySQL data source added in Port, using the MySQL SQL dialect.

MySQL uses a dialect close to standard SQL, but with some function and syntax differences. Backtick identifier usage, IFNULL(), GROUP_CONCAT(), and the date functions are MySQL-specific.

Strengths

Feature	Description
Common in MES	Many MES and SCADA systems use MySQL / MariaDB
Fast Reads	Fast SELECT queries with the MyISAM / InnoDB engines
Lightweight Setup	Lightweight and widely deployed; likely already running on your plant
Date Functions	Easy date handling with `YEAR()`, `MONTH()`, `DAY()`, `DATE_FORMAT()`
GROUP_CONCAT	Concatenate multiple rows into a single row

Common MySQL Patterns in Pirivision

Demo Factory — MySQL

The examples below use the Demo Factory MySQL dataset's demo_vardiya_uretim table (columns: ts, hat_id, makine_id, vardiya, urun_ailesi, uretim_adet, hedef). Port data source name: Demo Fabrika - MySQL (database app_db).

To generate the same data in your own environment → Synthetic Data Generation — MySQL

-- Last 50 production rows
SELECT ts, hat_id, makine_id, vardiya, urun_ailesi, uretim_adet
FROM   demo_vardiya_uretim
ORDER  BY ts DESC, hat_id
LIMIT  50;

-- Time range (parameterized)
SELECT ts, hat_id, uretim_adet
FROM   demo_vardiya_uretim
WHERE  ts >= {{start_date}}
  AND  ts <= {{end_date}}
ORDER  BY ts;

-- Last 7 days
SELECT ts, hat_id, uretim_adet
FROM   demo_vardiya_uretim
WHERE  ts >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER  BY ts DESC;

-- Daily summary
SELECT DATE(ts)         AS gun,
       SUM(uretim_adet) AS toplam,
       AVG(uretim_adet) AS ortalama,
       MAX(uretim_adet) AS maksimum
FROM   demo_vardiya_uretim
WHERE  ts >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP  BY DATE(ts)
ORDER  BY gun;

-- IFNULL / IF for conditional value
SELECT vardiya,
       IFNULL(SUM(uretim_adet), 0)                              AS toplam,
       IF(AVG(uretim_adet) >= 350, 'Above Target', 'Below Target') AS durum
FROM   demo_vardiya_uretim
WHERE  ts >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP  BY vardiya;

Important MySQL Functions

Category	Function	Example
Time	`NOW()`	`WHERE ts > NOW()`
Time	`CURDATE()`	`WHERE DATE(ts) = CURDATE()`
Time	`DATE_SUB()`	`DATE_SUB(NOW(), INTERVAL 7 DAY)`
Time	`DATE_FORMAT()`	`DATE_FORMAT(ts, '%Y-%m-%d')`
Null	`IFNULL()`	`IFNULL(value, 0)`
Conditional	`IF()`	`IF(val > 100, 'High', 'Normal')`
Text	`GROUP_CONCAT()`	`GROUP_CONCAT(col SEPARATOR ',')`
Identifier	Backtick	`table name`

Requirements

Requirement	Description
MySQL data source in Port	Connection must be tested
SELECT permission	The DB user must have permission on the queried tables

Guides

Guide	Description
Create MySQL Query	Write a new MySQL query in Compass
Edit MySQL Query	Update an existing query
Move Query	Move to a different folder
Duplicate Query	Copy a query
Delete Query	Remove a query

→ Create MySQL Query