You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-08-07 03:22:57 +03:00
36 lines
1.5 KiB
Plaintext
36 lines
1.5 KiB
Plaintext
DROP DATABASE IF EXISTS mcol_5074_db;
|
|
CREATE DATABASE mcol_5074_db;
|
|
USE mcol_5074_db;
|
|
CREATE TABLE `accnt` (
|
|
`acct_id` varchar(128) NOT NULL DEFAULT 'None',
|
|
`created_dt` datetime NOT NULL,
|
|
`mtn` varchar(21) NOT NULL DEFAULT 'None',
|
|
`accts` tinyint(4) NOT NULL DEFAULT 0
|
|
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4;
|
|
insert into accnt values
|
|
(1, '2021-12-11', "Partner", 5),
|
|
(2, '2021-12-12', "Retail", 4),
|
|
(1, '2021-12-11', "Partner", 5),
|
|
(3, '2021-12-11', "Mid-Market", 3),
|
|
(5, '2021-12-11', "PFG", 4),
|
|
(5, '2021-12-11', "PFG", 4),
|
|
(5, '2021-12-11', "Person Focal Group", 4),
|
|
(5, '2021-12-11', "Person Focal Group", 4);
|
|
select
|
|
mtn,
|
|
accts,
|
|
DATE_FORMAT(created_dt, '%Y-%m-%d') act_created_dt,
|
|
case when da.mtn = 'Partner' then count( acct_id) end as Partner_active_accts,
|
|
case when da.mtn = 'Retail' then count( acct_id) end as Retail_active_accts,
|
|
case when da.mtn = 'Enterprise' then count( acct_id) end as ENT_active_accts,
|
|
case when da.mtn in ('PFG','Person Focal Group') then count( acct_id) end +
|
|
case when da.mtn = 'Person Focal Group' then count( acct_id) end as PFG_active_accounts
|
|
from accnt da group by mtn, accts, act_created_dt order by mtn;
|
|
mtn accts act_created_dt Partner_active_accts Retail_active_accts ENT_active_accts PFG_active_accounts
|
|
Mid-Market 3 2021-12-11 NULL NULL NULL NULL
|
|
Partner 5 2021-12-11 2 NULL NULL NULL
|
|
Person Focal Group 4 2021-12-11 NULL NULL NULL 4
|
|
PFG 4 2021-12-11 NULL NULL NULL NULL
|
|
Retail 4 2021-12-12 NULL 1 NULL NULL
|
|
DROP DATABASE mcol_5074_db;
|