Randomly generate data under conditions for sqlite

Question

As part of creating a machine learning exercise, I have an sqlite database that looks like this: diagram The "Pizza", "DateDimension" and "Customer" tables are already populated, there are 5 different pizzas and 10 customers.

How could I generate random data for the sales table, but give conditions to create a pattern?

For example, on Monday/Tuesday a random sales figure between 15 and 25, on the other days of the week a random sales figure between 10 and 20, between 20 and 30 on the weekend.

Knowing that for a day, a sale of 15 pizzas would be for the customer of ID=1 a sale of 2 pizzas of ID=2 and 4 pizzas of ID=3, for the customer of ID=2 a sale of 3 pizzas of ID=2, and
for the ID=3 customer a sale of 3 pizzas of ID=4 and 3 pizzas of ID=2.

This makes a total of 15 pizzas, of which 8 ID=2 pizzas, 4 ID=3 pizzas, 3 ID=4 pizzas, divided between three customers.

I don't know if I'm clear enough in my explanations, basically we need a random number of pizza sales for each day, and that this number is itself randomly split between the types of pizza and the customers, under certain conditions in order to follow a pattern in the number of sales per day.

• J.D.: You probably want to generate your random data via application code as opposed to with database code (it'll be more easily accomplishable that way). You might want to consider asking this question on StackOverflow and tagging with whichever procedural programming language you're planning to use.

Answer 1

The random() function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807.

This range is a bit awkward to work with, but you can scale it to a floating-point value in the interval [0, 1) by using (RANDOM() + 9223372036854775808) / 18446744073709551616. Then the expression CAST((RANDOM() + 9223372036854775808) / 18446744073709551616 * ($HI - $LO + 1) + $LO AS INTEGER) gives you a random integer between $LO and $HI, inclusive. Just plug in whatever bounds you need for your random numbers.