gen_data.py

import random
from datetime import timedelta, date

NUM_ORDERS = lambda: 1_000
NUM_ITEMS = lambda: random.randint(1, 10)
QTY = lambda: random.randint(1, 10)

PRICE = lambda: random.randint(99, 999) + 0.99

DATE = lambda: timedelta(days=random.randint(0, 365)) + date(2020, 1, 1)

states = {
    "AL": "Alabama",
    "AK": "Alaska",
    "AS": "American Samoa",
    "AZ": "Arizona",
    "AR": "Arkansas",
    "CA": "California",
    "CO": "Colorado",
    "CT": "Connecticut",
    "DE": "Delaware",
    "DC": "District Of Columbia",
    "FM": "Federated States Of Micronesia",
    "FL": "Florida",
    "GA": "Georgia",
    "GU": "Guam",
    "HI": "Hawaii",
    "ID": "Idaho",
    "IL": "Illinois",
    "IN": "Indiana",
    "IA": "Iowa",
    "KS": "Kansas",
    "KY": "Kentucky",
    "LA": "Louisiana",
    "ME": "Maine",
    "MH": "Marshall Islands",
    "MD": "Maryland",
    "MA": "Massachusetts",
    "MI": "Michigan",
    "MN": "Minnesota",
    "MS": "Mississippi",
    "MO": "Missouri",
    "MT": "Montana",
    "NE": "Nebraska",
    "NV": "Nevada",
    "NH": "New Hampshire",
    "NJ": "New Jersey",
    "NM": "New Mexico",
    "NY": "New York",
    "NC": "North Carolina",
    "ND": "North Dakota",
    "MP": "Northern Mariana Islands",
    "OH": "Ohio",
    "OK": "Oklahoma",
    "OR": "Oregon",
    "PW": "Palau",
    "PA": "Pennsylvania",
    "PR": "Puerto Rico",
    "RI": "Rhode Island",
    "SC": "South Carolina",
    "SD": "South Dakota",
    "TN": "Tennessee",
    "TX": "Texas",
    "UT": "Utah",
    "VT": "Vermont",
    "VI": "Virgin Islands",
    "VA": "Virginia",
    "WA": "Washington",
    "WV": "West Virginia",
    "WI": "Wisconsin",
    "WY": "Wyoming"
}


STATE = lambda: random.choice(list(states.keys()))


PRICES = {}


def gen():
    for i in range(NUM_ORDERS()):
        order_num = 1_000_000 + i + 1
        dt = DATE()
        state = STATE()
        for j in range(NUM_ITEMS()):
            item_num = 100_000 + j + 1
            if item_num not in PRICES:
                PRICES[item_num] = PRICE()
            price = PRICES[item_num]
            qty = QTY()
            yield "INSERT INTO orders (orderid, itemid, qty, price, dt, state) VALUES (%i, %i, %i, %.2f, '%s', '%s');" % (order_num, item_num, qty, price, dt, state)

for i, sql in enumerate(gen()):
    print(sql)




"""
psql -h risingwave -U root -p 4566 dev
CREATE TABLE orders (orderid int, itemid int, qty int, price float, dt date, state varchar);

select avg(qty), itemid from orders group by itemid order by avg(itemid) desc;
select sum(qty), itemid from orders group by itemid order by sum(itemid) desc;

select sum(qty*price) as turnover, itemid from orders group by itemid order by turnover DESC;

select sum(qty*price) as turnover, state from orders group by state order by turnover DESC;

"""