0

I've this table in the main database

create table sales (
`updated_date_key` INT(10) UNSIGNED NOT NULL,
`store_id` BIGINT UNSIGNED NOT NULL,
`product_id` BIGINT UNSIGNED NOT NULL,
`category` INT(10) UNSIGNED NOT NULL,
`price` FLOAT NOT NULL,
`sold` BOOL NOT NULL,
 PRIMARY KEY (`store_id`, `product_id`),
)

When there is any change in the records, a set of events are created which is consumed in a different database (analytics database). Below we have a set of events created on two different dates. The event created on 20221012 (YYYYMMDD) can be considered as the initial state of the table. This event is consumed in a different database (analytics database).

event on : 20221012 (Three products were created)
{ updated_date_key : 20221012, store_id : 1, product_id: 123, category: 0, price: 500.12, sold: 0},  { updated_date_key : 20221012, store_id : 1, product_id: 456, category: 0, price: 100, sold: 0},  { updated_date_key : 20221012, store_id : 2, product_id: 678, category: 2, price: 200, sold: 0}

event on : 20221020 (Two products are sold)
{ updated_date_key : 20221020, store_id : 1, product_id: 123, category: 0, price: 500.12, sold: 1},  { updated_date_key : 20221020, store_id : 1, product_id: 456, category: 0, price: 100, sold: 1}

Expected outcome:

expected outcome is a snapshot that gives the count of sold and unsold products for a given store and category on a particular day.

  date_key, store_id, category  unsold_products, sold_products
  20221012       1      0         2               0    
  20221012       2      2         1               0      
  20221013       1      0         2               0    
  20221013       2      2         1               0 
  20221014       1      0         2               0    
  20221014       2      2         1               0 
  ...............
  20221020       1      0         0               2    
  20221020       2      2         1               0

Question:

One of the solution is to store all the revisions at the analytics database and then create a snapshot based on that. But storing all the revisions can create a huge table and we have resource constraints. Is it possible to create snapshot without storing the revisions in the analytics database purely based on the events that we receive? Is it possible to store only the pre-aggregated results of the events like count of sold, unsold item based on event and then create snapshot based on this pre-aggregated revision table? Is there more efficient way of creating snapshot based on revision table?

Improve this question
6
  • Please add some scripts with testdata. Best would be in a fiddlr.
    – Peter
    Jan 18 at 7:11
  • Consider using IODKU to do the increments when needed. Perhaps use a stored proc or trigger.
    – Rick James
    Jan 18 at 7:15
  • @RickJames how does that help in pre-aggregation or creating snapshot? can you elaborate?
    – SQLProfiler
    Jan 18 at 7:47
  • @SQLProfiler - I can discuss Summary Tables, where you have a big dataset of, say, sensor data, and you want to have daily summary information. Or Sales, with subtotals by the week. Is that what you are talking about? They do involve "aggregation", but I don't understand "snapshot" in this context.
    – Rick James
    Jan 18 at 16:49
  • @RickJames the data which I've is a revision data i.e. updates on certain dates. let's say there were three products (x, y, z) on date d1 and product 1 and product 2 were sold on date d22. the revision table will have two records. one for d1 and one for d22. snapshot will show the status of products on d1, d2, d3, ....d22. so on d2 the records will be same as d1 as there are no changes until d22. The aggregation is about count of the products (sold, unsold...) on the given dates.
    – SQLProfiler
    Jan 18 at 17:13

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.