tables['transactions'] = Table(
df=pd.DataFrame(t),
pkey_col='t_id',
fkey_col_to_pkey_table={
'customer_key': 'customers',
'item_key': 'products',
'store_key': 'stores'
},
time_col='date'
)

class EcommerceDataBase(Dataset):
#創(chuàng)建你自己的數(shù)據集的示例:??https://github.com/snap-stanford/relbench/blob/main/tutorials/custom_dataset.ipynb?

val_timestamp = pd.Timestamp(year=2018, month=1, day=1)
test_timestamp = pd.Timestamp(year=2020, month=1, day=1)

def make_db(self) -> Database:

tables = {}

customers = load_csv_to_db(BASE_DIR + '/customer_dim.csv').drop(columns=['contact_no', 'nid']).rename(columns={'coustomer_key': 'customer_key'})
stores = load_csv_to_db(BASE_DIR + '/store_dim.csv').drop(columns=['upazila'])
products = load_csv_to_db(BASE_DIR + '/item_dim.csv')
transactions = load_csv_to_db(BASE_DIR + '/fact_table.csv').rename(columns={'coustomer_key': 'customer_key'})
times = load_csv_to_db(BASE_DIR + '/time_dim.csv')

t = transactions.merge(times[['time_key', 'date']], on='time_key').drop(columns=['payment_key', 'time_key', 'unit'])
t['date'] = pd.to_datetime(t.date)
t = t.reset_index().rename(columns={'index': 't_id'})
t['quantity'] = t.quantity.astype(int)
t['unit_price'] = t.unit_price.astype(float)
products['unit_price'] = products.unit_price.astype(float)
t['total_price'] = t.total_price.astype(float)

print(t.isna().sum(axis=0))
print(products.isna().sum(axis=0))
print(stores.isna().sum(axis=0))
print(customers.isna().sum(axis=0))

tables['products'] = Table(
df=pd.DataFrame(products),
pkey_col='item_key',
fkey_col_to_pkey_table={},
time_col=None
)

tables['customers'] = Table(
df=pd.DataFrame(customers),
pkey_col='customer_key',
fkey_col_to_pkey_table={},
time_col=None
)

tables['transactions'] = Table(
df=pd.DataFrame(t),
pkey_col='t_id',
fkey_col_to_pkey_table={
'customer_key': 'customers',
'item_key': 'products',
'store_key': 'stores'
},
time_col='date'
)

tables['stores'] = Table(
df=pd.DataFrame(stores),
pkey_col='store_key',
fkey_col_to_pkey_table={}
)

return Database(tables)

df = duckdb.sql(f"""
select
timestamp,
customer_key,
sum(total_price) as revenue
from
timestamp_df t
left join
transactions ta
on
ta.date <= t.timestamp + INTERVAL '{self.timedelta}'
and ta.date > t.timestamp
group by timestamp, customer_key
""").df().dropna()

class CustomerRevenueTask(EntityTask):
# 自定義任務示例:??https://github.com/snap-stanford/relbench/blob/main/tutorials/custom_task.ipynb??


task_type = TaskType.REGRESSION
entity_col = "customer_key"
entity_table = "customers"
time_col = "timestamp"
target_col = "revenue"
timedelta = pd.Timedelta(days=30) # 我們想要預測未來的收入。
metrics = [r2, mae]
num_eval_timestamps = 40

def make_table(self, db: Database, timestamps: "pd.Series[pd.Timestamp]") -> Table:

timestamp_df = pd.DataFrame({"timestamp": timestamps})

transactions = db.table_dict["transactions"].df

df = duckdb.sql(f"""
select
timestamp,
customer_key,
sum(total_price) as revenue
from
timestamp_df t
left join
transactions ta
on
ta.date <= t.timestamp + INTERVAL '{self.timedelta}'
and ta.date > t.timestamp
group by timestamp, customer_key
""").df().dropna()

print(df)

return Table(
df=df,
fkey_col_to_pkey_table={self.entity_col: self.entity_table},
pkey_col=None,
time_col=self.time_col,
)

from typing import List, Optional
from sentence_transformers import SentenceTransformer
from torch import Tensor


class GloveTextEmbedding:
def __init__(self, device: Optional[torch.device
] = None):
self.model = SentenceTransformer(
"sentence-transformers/average_word_embeddings_glove.6B.300d",
device=device,
)

def __call__(self, sentences: List[str]) -> Tensor:
return torch.from_numpy(self.model.encode(sentences))

from torch_frame.config.text_embedder import TextEmbedderConfig
from relbench.modeling.graph import make_pkey_fkey_graph

text_embedder_cfg = TextEmbedderConfig(
text_embedder=GloveTextEmbedding(device=device), batch_size=256
)

data, col_stats_dict = make_pkey_fkey_graph(
db,
col_to_stype_dict=col_to_stype_dict,  # speficied column types
text_embedder_cfg=text_embedder_cfg,  # our chosen text encoder
cache_dir=os.path.join(
root_dir, f"rel-ecomm_materialized_cache"
),  # store materialized graph for convenience
)