![ืืื ืฆื ืืึทืืืขื ืืึทื ืืฆืขืจ ืงืึธืืึธืจืฅ ืืื ืืจืึทืคืก ืืื ืืจืึทืคืึทื ืึท [+ ืืึธืงืงืขืจ ืืืื ืืื ืืืึทืฉืคึผืื]](https://prohoster.info/wp-content/plugins/wp-fastest-cache-premium/pro/images/blank.gif)
ืืื ืืืจ ืกืึทืืืื ืื ืคึผืจืึธืืืขื ืคืื ืืืืืฉืืืึทืืืืืื ื ืืึทื ืืฆืขืจ ืงืึธืืึธืจืฅ ืืื ืื ืคึผืจืึธืืึธืคึผืืื ืืื ืกื ื ืืฆื Grafana.
- ืึท ืฉืืึทืจืง ืืื ืกื ืืื ืึท ืืจืืืก ื ืืืขืจ ืคืื ื ืืฆืขืจืก. ืืืืขืจ ืื 10 ืืึธืจ ืคืื ืึธืคึผืขืจืึทืฆืืข, ืื ื ืืืขืจ ืคืื ืจืขืืืฉืืกืืจืืืฉืึทื ื ืืื ืื ืกืืกืืขื ืืื ืืงืกืืืื ืึท ืืืืืึธื. ืืขื ืข ืืืืก ืืึธืื ืืขืคึผืืึธื ืืขืจื ืขื ืืขื ืืึทืืื ืื ืืก ืืืืกื ืึทื ืื ืืขื ืืข ืคืื โโื ืืฆืขืจืก ืืื ืืืืึทื ืคืื ืืึธืืืึทืืืฉืื ืืึทืก.
ืขืืขืฅ ืืื ืืื ืืืื ืืขืฉืจืืื ืืื "ืืืืืื ืืืื ืืขืฉืืืคื". ืขืืขืฆืขืจ ืคืืจืืขืกื ืืืืขืจ ืคึผืึทืจืึธื ืืื ืจืขืืืกืืจืืจื ืึท ืคึผืึธืจ ืืขืจ ืืึธื ืืื ืื ืืืืฃ ืคืื ืืขืงืก ืืืฉืื. ืขืืขืฆืขืจ ืืจืขื ืื ืืขืื ืฆื ืื ืงืึทืฉ ืจืขืืืกืืจืืจื, ืืื ืขืืขืฆืขืจ ืืขืงืืืขื ืคึฟืึทืจ ืคืจืื . ืืื ืขืก ืืืึธืื ืืืื ืคืืึทื ืฆื ืืึทืงืืืขื ืขืืืขืืข ื ืืฅ ืคืื ืึทืืขืืขื.
ืืืืฃ ืึทืืึท ืืจืืืก ืืึทืื ืฉืืขืื ืืื ืืื ืืืขืจ, ืึทื ืึทืืืืืื ื ืื ื ืึทืืืจ ืคืื ืึท ืืืื ืืึทื ืืฆืขืจ ืืื ืืึทืื ืืืงืจืึธ-ืืืกืืืฉืึทื ื ืืื ืืืืืกื. ืืืขืจ ืงืึทืืฉืื ื ืืจืขื ืืก ืืื ืืจืืขืื ืืื ืืจืืืก ืืจืืคึผืขืก ืืื ืืขืืืขื ืืื ื ืืืืืง. ืืืึธืก ืืื ืคึผืื ืงื ืืืึธืก ืืืจ ืืึธื.
Summary
- ืืืึธืก ืืื ืงืึธืืึธืจื ืึทื ืึทืืืกืืก ืืื ืืืึธืก ืืื ืขืก ืืืจืฃ?
- ืืื ืฆื ืืึทืื ืงืึธืืึธืจืฅ ืืืื ืืืืฉ ืคืื ืืึทื ืืฆืขืจ ืจืขืืืกืืจืึทืฆืืข ืืื SQL.
- ืืื ืฆื ืึทืจืืืขืจืคืืจื ืงืึธืืึธืจืฅ ืฆื .
ืืืื ืืืจ ืฉืืื ืืืืกื ืืืึธืก ืงืึธืืึธืจื ืึทื ืึทืืืกืืก ืืื ืืื ืืื ืฆื ืืึธื ืืึธืก ืืื SQL, ืืึธืคึผืงืขื ืืืืื ืฆื ืื ืืขืฆืืข ืึธืคึผืืืืืื ื.
1. ืืืึธืก ืืื ืงืึธืืึธืจื ืึทื ืึทืืืกืืก ืืื ืืืึธืก ืืื ืขืก ืืืจืฃ?
ืงืึธืืึธืจื ืึทื ืึทืืืกืืก ืืื ืึท ืืขืืึธื ืืืืืจื ืืืืฃ ืงืึทืืคึผืขืจืื ื ืคืึทืจืฉืืืขื ืข ืืจืืคึผืขืก (ืงืึธืืึธืจืฅ) ืคืื ื ืืฆืขืจืก. ืจืืึฟ ืึธืคื, ืืื ืืืขืจ ืืจืืคึผืขืก ืืขื ืขื ืืขืฉืืคื ืืืจื ืื ืืืึธื ืึธืืขืจ ืืืืฉ ืืื ืืืึธืก ืืขืจ ืืึทื ืืฆืขืจ ืื ืืขืืืืื ื ืืฆื ืื ืกืขืจืืืืก. ืคืื ืืึธ ืื ืืึทื ืืฆืขืจ 'ืก ืืขืื ืืื ืงืึทืืงืืึทืืืืืื, ืืื ืืึธืก ืืื ืึท ืืจืืื ืืืืฃ ืืขืจ ืืืืข ืคืื โโืืืึธืก ืงืขื ืขื ืืืื ืืืจืืืขืงืึธืื ืืึทื ืฅ ืงืึธืืคึผืืืฆืืจื ืึทื ืึทืืืกืืก. ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืคึฟืึทืจืฉืืืื:
- ืืื ืืื ืืขืจ ืึทืงืืืึทืืืฉืึทื ืงืึทื ืึทื ืืฉืคึผืขื ืื ืืขืื ืคืื ืื ืืึทื ืืฆืขืจ;
- ืืื ืื ื ืืฆื ืคืื ืงืืื ืฉืืจืื ืึธืืขืจ ืืื ืกื ืึทืคืขืงืฅ ืืขืื;
- ืืื ืื ืงืึทืืขืจ ืคืื ืฉืืจืื X ืึทืคืขืงืืึทื ืื ืืขืื ืงืึทืืคึผืขืจื ืืื ืืขืฆืืข ืืึธืจ.
2. ืืื ืฆื ืืึทืื ืงืึธืืึธืจืฅ ืืื ืกืงื?
ืื ืืจืืืก ืคืื ืืขื ืึทืจืืืงื ืืื ืกืืืื ืืึธื ื ืื ืืึธืื ืืื ืื ืฆื ืคืึธืจืฉืืขืื ืืื ืืืขืจ ืคืึทืงืืืฉ ืืึทืื ืืึธ - ืืื ืื ืคึผืจืึธืืข ืืึทืืคึผ, ืกืืึทืืืกืืืง ืคึฟืึทืจ ืึท ืืึธืจ ืืื ืึท ืืึทืื: 1200 ื ืืฆืขืจืก ืืื 53 ืืจืึทื ืืึทืงืฉืึทื ื. ืึทืืื ืึทื ืืืจ ืงืขื ืขื ืฉืคึผืืื ืืื ืื ืืึทืื, ืืืจ ืืึธืื ืฆืืืขืืจืืื ืึท ืืึธืงืงืขืจ ืืืื ืืื MySQL ืืื Grafana ืืื ืืืึธืก ืืืจ ืงืขื ืขื ืืขืจืคืึทืจืื ื ืขืก ืึทืืข ืืื. ืืื ืง ืฆื GitHub ืืื ืื ืกืืฃ ืคืื ืืขื ืึทืจืืืงื.
ืืื ืืึธ ืืืจ ืืืขืื ืืืืึทืื ืื ืฉืึทืคืื ื ืคืื ืงืึธืืึธืจืฅ ื ืืฆื ืึท ืกืืืคึผืืึทืคืืื ืืืึทืฉืคึผืื.
ืืื ืก ืืืขืจื ืขืืขื ืืืจ ืืึธืื ืึท ืืื ืกื. ืืืืขืจื ืคืึทืจืฉืจืืึทืื ืืึธืจื ืืื ืคืึทืจืืจืขื ืืขื ืืขืื ืืืืฃ ืกืขืจืืืืกืขืก. ืืืืขืจ ืฆืืึทื, ื ืืฆืขืจืก ืคืึทืื ืืืืก. ืืืจ ืืืืื ืฆื ืืขืคึฟืื ืขื ืืืืก ืืื ืืึทื ื ืืืืขืจื ืืขืื, ืืื ืืื ืคืืืข ืคืื โโืืื ืคืึทืื ืึทืืืขืง ื ืึธื ืื 1 ืืื 2 ืืืืฉ ืคืื ื ืืฆื ืื ืืื ืกื.
ืฆื ืขื ืืคึฟืขืจื ืื ืคึฟืจืืื, ืืืจ ืืึทืจืคึฟื ืฆื ืืืืขื ืงืึธืืึธืจืฅ ืืืืืจื ืืืืฃ ืื ืืืืฉ ืคืื ืจืขืืืกืืจืึทืฆืืข. ืืืจ ืืืขืื ืืขืกืื ืึทืงืืืืืืืขื ืืืื ืืืฆืืืช ืืื ืืขืืขืจ ืืืืฉ. ืึทื ืฉืืึธื ืืืฆืืืช, ืขืก ืงืขื ืืืื ืึธืจืืขืจืก, ืึทืืึธื ืขืืขื ื ืคืื ืึธืืขืจ ืงืืื ืื ืืขืจืข ืฆืืื-ืืืืืจื ืืขืืืงืืื.
ืขืจืฉื ืืึทืืข
ืื ืืืืฉืคืืื ืืขื ืขื ืืขืืืื ืืื MySQL, ืึธืืขืจ ืคึฟืึทืจ ืื ืืขืจืข DBMS ืขืก ืืึธื ื ืืฉื ืืืื ืงืืื ืืึทืืืึทืืืง ืืืคืขืจืึทื ืกืื.
ืืึทื ืืฆืขืจ ืืืฉ - ืืกืขืจืก:
ืืึทื ืืฆืขืจ ืฉืืึทื
ืจืขืืืกืืจืึทืฆืืข ืืึธื
1
2019-01-01
2
2019-02-01
3
2019-02-10
4
2019-03-01
ืงืึธืกื ืืืฉ - ืืืืื ื:
ืืึทื ืืฆืขืจ ืฉืืึทื
ืืึทืืข
ืกืึทืืึทืงื
1
2019-01-02
11
1
2019-02-22
11
2
2019-02-12
12
3
2019-02-11
13
3
2019-03-11
13
4
2019-03-01
14
4
2019-03-02
14
ืืืืกืงืืืึทืื ืึทืืข ืืึทื ืืฆืขืจ ืฉืจืืืื-ืึธืคืก ืืื ืจืขืืืกืืจืึทืฆืืข ืืึธื:
SELECT
b.userId,
b.Date,
u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
ืืขืจ ืจืขืืืืืึทื:
ืืึทื ืืฆืขืจ ืฉืืึทื
ืืึทืืข
ืจืขืืืกืืจืึทืฆืืข ืืึธื
1
2019-01-02
2019-01-02
1
2019-02-22
2019-01-02
2
2019-02-12
2019-02-01
3
2019-02-11
2019-02-10
3
2019-03-11
2019-02-10
4
2019-03-01
2019-03-01
4
2019-03-02
2019-03-01
ืืืจ ืืืืขื ืงืึธืืึธืจืฅ ืืืจื ืืืืฉ; ืฆื ืืึธื ืืึธืก, ืืืจ ืงืึธื ืืืขืจื ืึทืืข ืืึทืืขืก ืืื ืืืฉืื:
DATE_FORMAT(Date, '%Y-%m')ืืืฆื ืืืจ ืืึทืจืคึฟื ืฆื ืืืืกื ืืื ืคืืืข ืืืฉืื ืืขืจ ืืึทื ืืฆืขืจ ืืื ืืขืืืขื ืึทืงืืืื - ืืึธืก ืืื ืืขืจ ืืืืืง ืฆืืืืฉื ืื ืืืืฉ ืคืื ืืขืืื ืืื ืื ืืืืฉ ืคืื ืจืขืืืกืืจืึทืฆืืข. MySQL ืืื ืึท ืคืื ืงืฆืืข PERIOD_DIFF () - ืื ืืืืืง ืฆืืืืฉื ืฆืืืื ืืืฉืื. ืืืื PERIOD_DIFF() ืฆื ืืขืจ ืืงืฉื:
SELECT
b.userId,
DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
ืืึทื ืืฆืขืจ ืฉืืึทื
ืืืืื ื ืืึธื ืื
ืจืขืืืกืืจืึทืฆืืข ืืึธื
MonthsDiff
1
2019-01
2019-01
0
1
2019-02
2019-01
1
2
2019-02
2019-02
0
3
2019-02
2019-02
0
3
2019-03
2019-02
1
4
2019-03
2019-03
0
4
2019-03
2019-03
0
ืืืจ ืฆืืืื ืื ืืืืขืจื ืึทืงืืืืืืืืื ืืื ืืขืืขืจ ืืืืฉ - ืืืจ ืืจืืคึผืข ืื ืจืขืงืึธืจืืก ืืืจื BillingMonth, RegistrationMonth ืืื MonthsDiff:
SELECT
COUNT(DISTINCT(b.userId)) AS UsersCount,
DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
GROUP BY BillingMonth, RegistrationMonth, MonthsDiff
ืืขืจ ืจืขืืืืืึทื:
UsersCount
ืืืืื ื ืืึธื ืื
ืคืึทืจืฉืจืืืืื ื ืืึธื ืื
MonthsDiff
1
2019-01
2019-01
0
1
2019-02
2019-01
1
2
2019-02
2019-02
0
1
2019-03
2019-02
1
1
2019-03
2019-03
0
ืืื ืืื ืืืจ, ืคืขืืจืืืจ ืืื ืืึทืจืฅ, ืืืื ืขืจ ื ืืึท ืืึทื ืืฆืขืจ ืืจืืืก ืืขืืขืจ - MonthsDiff = 0. ืืืื ืืื ืืืจ ืืึทื ืืฆืขืจ ืืื ืึทืงืืืื ืืื ืคืขืืจืืืจ - ืจืขืืืกืืจืึทืืืึธื ืืึธื ืื = 2019-01, BillingMonth = 2019-02, ืืื ืืืื ืคืขืืจืืืจ ืืึทื ืืฆืขืจ ืืื ืึทืงืืืื ืืื ืืึทืจืฅ.
ืืขืืืืื ืืืขื, ืคึผืึทืืขืจื ื ืืขื ืขื ืืขืกืขืจ ืงืขื ืืืง ืืื ืึท ืืจืืืก ืืึทืื ืฉืืขืื.
ืืื ืฆื ืึทืจืืืขืจืคืืจื ืงืึธืืึธืจืฅ ืฆื Grafana
ืืืจ ืืึธืื ืืขืืขืจื ื ืืื ืฆื ืืึทืื ืงืึธืืึธืจืื, ืึธืืขืจ ืืืขื ืขืก ืืขื ืขื ืึท ืคึผืืึทืฅ ืคืื ืจืขืงืึธืจืืก, ืขืก ืืื ื ืืฉื ืืจืื ื ืฆื ืึทื ืึทืืืื ืืื. ืจืขืงืึธืจืืก ืงืขื ืขื ืืืื ืืงืกืคึผืึธืจืืึทื ืฆื ืขืงืกืกืขื ืืื ืืขืฉืืคื ืืื ืฉืืื ืืืฉื, ืึธืืขืจ ืืึธืก ืืื ื ืืฉื ืืื ืืืขืจ ืืืคึฟื!
ืงืึธืืึธืจืฅ ืงืขื ืขื ืืืื ืืขืืืืื ืืื ืึท ืื ืืขืจืึทืงืืืื ืืจืึทืคืืง ืืื .
ืฆื ืืึธื ืืึธืก, ืืืจ ืืืืื ืื ืื ืืขืจ ืึธื ืคึฟืจืขื ืฆื ืืขืจ ืื ืืึทืื ืืื ืึท ืคึฟืึธืจืืึทื ืคึผืึทืกืืง ืคึฟืึทืจ Grafana:
SELECT
DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
SUM(s.Users) AS value,
s.RegistrationMonth AS metric
FROM (
## ััะฐััะน ะทะฐะฟัะพั, ะฒะพะทะฒัะฐัะฐััะธะน ะบะพะณะพััั
SELECT
COUNT(DISTINCT(b.userId)) AS Users,
DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
WHERE
u.RegistrationDate BETWEEN '2018-01-01' AND CURRENT_DATE
GROUP BY
BillingMonth, RegistrationMonth, MonthsDiff
) AS s
GROUP BY
time_sec, metric
ืืื ืฆืืคึฟืขืืืงืขืจ ืื ืืึทืื ืฆื Grafana.
ืืืึทืฉืคึผืื ืืจืึทืคืืง ืคืื :
ืจืืจ ืืื ืืืื ืืขื ื:
ืืื ืึท ืืึธืงืงืขืจ ืืืื ืืื MySQL ืืื Grafana ืืืึธืก ืืืจ ืงืขื ืขื ืืืืคื ืืืืฃ ืืืื ืงืึธืืคึผืืืืขืจ. ืื ืืึทืืึทืืืืก ืฉืืื ืึผืืื ืืขืืึธ ืืึทืื ืคึฟืึทืจ ืึท ืืึธืจ ืืื ืึท ืืึทืื, ืคึฟืื ืืื ืืืจ 2018 ืฆื ืืืื 2019.
ืืืื ืืืจ ืืืืื, ืืืจ ืงืขื ืขื ืืึธืื ืืืื ืืึทืื ืืื ืืขื ืืืื.
ืคึผืก ืืจืืืงืืขื ืืืขืื ืงืึธืืึธืจื ืึทื ืึทืืืกืืก ืืื SQL:
ืืงืืจ: www.habr.com