##  Deephaven - Joins Recipes Notebook - Python  
##  https://docs.deephaven.io/


from deephaven import *
# See print(sorted(dir())) or help('deephaven') for full namespace contents.


# generate shared tables
trades = db.t("LearnDeephaven", "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").update("TotalShares=(long)TotalShares")
summaryTrim = summary.tail(9)


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


# Natural Join Recipe
nj = trades.naturalJoin(summaryTrim, "Sym", "AvgPrice, NewNameTotShares = TotalShares")
#  Note: In this case, TotalShares from the right table is renamed  
#  to NewNameTotShares when it is joined to the left table.  This is    
#  important for cases where the left table already has TotalShares
#  as a column name, for example. 


# Join
j1 = trades.join(summaryTrim, "Sym", "AvgPrice, TotalShares")
duplicate = ttools.emptyTable(1).updateView("Sym=`MSFT`", "AvgPrice=72.9", "TotalShares=(long)23")
# Note: using (long)23 ensures 23 is interpreted as type long

summaryDup = ttools.merge(summary, duplicate)
j2 = trades.join(summaryDup, "Sym", "AvgPrice, TotalShares")


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


# As-of Join and Reverse As-of Join
trades = db.t("LearnDeephaven", "StockTrades")\
   .where("Date=`2017-08-25`")\
   .view("Sym", "TradeTime=ExchangeTimestamp", "Last")

quotes = db.t("LearnDeephaven", "StockQuotes")\
   .where("Date=`2017-08-25`")\
   .sort("ExchangeTimestamp")\
   .view("Sym", "QuoteTime=ExchangeTimestamp", "Bid", "Ask")
    
quotesMid = quotes.updateView("Mid=(Bid+Ask)/2")

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

753a3c4c0b303a8208346261d9275e8ca0e29b53
<mask token>

<mask token>
trades = db.t('LearnDeephaven', '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').update('TotalShares=(long)TotalShares')
summaryTrim = summary.tail(9)
ej = trades.exactJoin(summary, 'Sym', 'AvgPrice, TotalShares')
nj = trades.naturalJoin(summaryTrim, 'Sym',
    'AvgPrice, NewNameTotShares = TotalShares')
j1 = trades.join(summaryTrim, 'Sym', 'AvgPrice, TotalShares')
duplicate = ttools.emptyTable(1).updateView('Sym=`MSFT`', 'AvgPrice=72.9',
    'TotalShares=(long)23')
summaryDup = ttools.merge(summary, duplicate)
j2 = trades.join(summaryDup, 'Sym', 'AvgPrice, TotalShares')
lj = summary.leftJoin(trades, 'Sym', 'Last')
trades = db.t('LearnDeephaven', 'StockTrades').where('Date=`2017-08-25`').view(
    'Sym', 'TradeTime=ExchangeTimestamp', 'Last')
quotes = db.t('LearnDeephaven', 'StockQuotes').where('Date=`2017-08-25`').sort(
    'ExchangeTimestamp').view('Sym', 'QuoteTime=ExchangeTimestamp', 'Bid',
    'Ask')
quotesMid = quotes.updateView('Mid=(Bid+Ask)/2')
aj = trades.aj(quotesMid, 'Sym=Sym,TradeTime=QuoteTime', 'Mid, QuoteTime')
raj = trades.raj(quotesMid, 'Sym=Sym,TradeTime=QuoteTime', 'Mid, QuoteTime')

from deephaven import *
trades = db.t('LearnDeephaven', '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').update('TotalShares=(long)TotalShares')
summaryTrim = summary.tail(9)
ej = trades.exactJoin(summary, 'Sym', 'AvgPrice, TotalShares')
nj = trades.naturalJoin(summaryTrim, 'Sym',
    'AvgPrice, NewNameTotShares = TotalShares')
j1 = trades.join(summaryTrim, 'Sym', 'AvgPrice, TotalShares')
duplicate = ttools.emptyTable(1).updateView('Sym=`MSFT`', 'AvgPrice=72.9',
    'TotalShares=(long)23')
summaryDup = ttools.merge(summary, duplicate)
j2 = trades.join(summaryDup, 'Sym', 'AvgPrice, TotalShares')
lj = summary.leftJoin(trades, 'Sym', 'Last')
trades = db.t('LearnDeephaven', 'StockTrades').where('Date=`2017-08-25`').view(
    'Sym', 'TradeTime=ExchangeTimestamp', 'Last')
quotes = db.t('LearnDeephaven', 'StockQuotes').where('Date=`2017-08-25`').sort(
    'ExchangeTimestamp').view('Sym', 'QuoteTime=ExchangeTimestamp', 'Bid',
    'Ask')
quotesMid = quotes.updateView('Mid=(Bid+Ask)/2')
aj = trades.aj(quotesMid, 'Sym=Sym,TradeTime=QuoteTime', 'Mid, QuoteTime')
raj = trades.raj(quotesMid, 'Sym=Sym,TradeTime=QuoteTime', 'Mid, QuoteTime')

##  Deephaven - Joins Recipes Notebook - Python  
##  https://docs.deephaven.io/


from deephaven import *
# See print(sorted(dir())) or help('deephaven') for full namespace contents.


# generate shared tables
trades = db.t("LearnDeephaven", "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").update("TotalShares=(long)TotalShares")
summaryTrim = summary.tail(9)


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


# Natural Join Recipe
nj = trades.naturalJoin(summaryTrim, "Sym", "AvgPrice, NewNameTotShares = TotalShares")
#  Note: In this case, TotalShares from the right table is renamed  
#  to NewNameTotShares when it is joined to the left table.  This is    
#  important for cases where the left table already has TotalShares
#  as a column name, for example. 


# Join
j1 = trades.join(summaryTrim, "Sym", "AvgPrice, TotalShares")
duplicate = ttools.emptyTable(1).updateView("Sym=`MSFT`", "AvgPrice=72.9", "TotalShares=(long)23")
# Note: using (long)23 ensures 23 is interpreted as type long

summaryDup = ttools.merge(summary, duplicate)
j2 = trades.join(summaryDup, "Sym", "AvgPrice, TotalShares")


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


# As-of Join and Reverse As-of Join
trades = db.t("LearnDeephaven", "StockTrades")\
   .where("Date=`2017-08-25`")\
   .view("Sym", "TradeTime=ExchangeTimestamp", "Last")

quotes = db.t("LearnDeephaven", "StockQuotes")\
   .where("Date=`2017-08-25`")\
   .sort("ExchangeTimestamp")\
   .view("Sym", "QuoteTime=ExchangeTimestamp", "Bid", "Ask")
    
quotesMid = quotes.updateView("Mid=(Bid+Ask)/2")

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