We're a new Adtech company and I was planning to design a database where I'll pull all the data to a single table and then make new tables with a materialized views for others to generate multiple reports.
Say we have Inventory, impression, views for multiple reasons.
Our main table looks like this, to recreate this
CREATE TABLE report.empty_summing (times DateTime64,inventory_id String,city Nullable(String), country Nullable(String),inventory Int32 default 0, impression Int32 default 0, views Int32 default 0) ENGINE=SummingMergeTree() primary key inventory_id;
When a request comes from google ADX to our Adengine , it has a unique id which is "inventory_id" and other parameters like country, city..... other string type parameters are inserted.
When 3 types of data are inserted it looks like this.
You can see that Every row have their values inserted but I want to
Our inventory request insert looks like this.
INSERT INTO report.empty_summing (times,inventory_id,country,city,inventory,impression,views) VALUES (now(),'7120426e6abd0b04ec8c777460a78bdf4b9de0','Bangladesh','Dhaka',1,0,0);
Our impression insert looks like this.
INSERT INTO report.empty_summing (times,inventory_id,impression) VALUES (now(),'7120426e6abd0b04ec8c777460a78bdf4b9de0',1);
Our view insert looks like this.
INSERT INTO report.empty_summing (times,inventory_id,views) VALUES (now(),'7120426e6abd0b04ec8c777460a78bdf4b9de0',1);
You can see that "inventory_id" is the same for all these rows. is there any DB engine or any technique I can use where data will be merged and look like this?
Help is much appreciated. thanks in advance!
