# **************      Cookbook / Join Methods / Python      ************** 
# **************   Copyright 2019 Deephaven Data Labs, LLC  ************** 

# This notebook is part of the Deephaven Query Cookbook. For more information, 
# please refer to the Deephaven Documentation Portal at 
# https://docs.deephaven.io/latest/Content/quickReference/cookbook/cookbook.htm


# Exact Join and Natural Join Recipe

import illumon.iris.ComboAggregateFactory as caf

trades = db.t("LearnIris", "StockTrades")\
    .where("Date=`2017-08-25`")\
    .headBy(3, "Sym")\
    .view("Sym", "Last", "Size")

summary = trades.by(caf.AggCombo(caf.AggAvg("AvgPrice=Last"), caf.AggSum("TotalShares=Size")), "Sym")

ej = trades.exactJoin(summary, "Sym", "AvgPrice, TotalShares")

summaryTrim = summary.tail(9)

nj = trades.naturalJoin(summaryTrim, "Sym", "AvgPrice, TotalShares")
        


# Join

import illumon.iris.ComboAggregateFactory as caf
import illumon.iris.TableTools as tt

trades = db.t("LearnIris", "StockTrades")\
    .where("Date=`2017-08-25`")\
    .headBy(3, "Sym")\
    .view("Sym", "Last", "Size")

summary = trades.by(caf.AggCombo(caf.AggAvg("AvgPrice=Last"), caf.AggSum("TotalShares=Size")), "Sym")

summaryTrim = summary.tail(9)

j1 = trades.join(summaryTrim, "Sym", "AvgPrice, TotalShares")

duplicate = tt.emptyTable(1)\
    .updateView("Sym=`MSFT`", "AvgPrice=72.9", "TotalShares=23")

summaryDup = tt.merge(summary, duplicate)

j2 = trades.join(summaryDup, "Sym", "AvgPrice, TotalShares")
        


# Left Join

trades = db.t("LearnIris", "StockTrades").where("Date=`2017-08-25`")\
    .headBy(3, "Sym")\
    .view("Sym", "Last")

summary = trades.view("Sym", "AvgPrice=Last").avgBy("Sym")

lj = summary.leftJoin(trades, "Sym", "Last")
        


# As-of Join and Reverse As-of Join

trades = db.t("LearnIris", "StockTrades")\
    .where("Date=`2017-08-25`")\
    .view("Sym", "TradeTime=ExchangeTimestamp", "Last")

quotes = db.t("LearnIris", "StockQuotes")\
    .where("Date=`2017-08-25`")\
    .view("Sym", "QuoteTime=ExchangeTimestamp", "Bid", "Ask")

quotesMid = quotes.updateView("Mid=(Bid+Ask)/2").sort("Sym")

aj = trades.aj(quotesMid, "TradeTime=QuoteTime", "Mid, QuoteTime")

raj = trades.raj(quotesMid, "TradeTime=QuoteTime", "Mid, QuoteTime")