finance-dbt/models/dw/dw.fact_stock_daily.sql
沈楠 ec07e9ca89
Some checks failed
MJN/finance-dbt/pipeline/head There was a failure building this commit
release: 0.2.0
adjust fields
2024-07-23 10:33:41 +08:00

52 lines
1.9 KiB
SQL

{{
config(
materialized='incremental',
engine='MergeTree',
order_by='(date_id, ts_code)',
unique_key=['date_id', 'ts_code'],
incremental_strategy='delete+insert',
query_settings={
"join_algorithm": "'full_sorting_merge'",
}
)
}}
Select
StgDaily.ts_code As ts_code,
DimDate.date_id As date_id,
StgDaily.open As open,
StgDaily.high As high,
StgDaily.low As low,
StgDaily.close As close,
StgDaily.pre_close As pre_close,
StgDaily.change As change,
StgDaily.pct_chg As pct_chg,
StgDaily.vol As vol,
StgDaily.amount As amount,
StgAdjFactor.adj_factor As adj_factor,
StgDailyBasic.turnover_rate As turnover_rate,
StgDailyBasic.turnover_rate_f As turnover_rate_f,
StgDailyBasic.volume_ratio As volume_ratio,
StgDailyBasic.pe As pe,
StgDailyBasic.pe_ttm As pe_ttm,
StgDailyBasic.pb As pb,
StgDailyBasic.ps As ps,
StgDailyBasic.ps_ttm As ps_ttm,
StgDailyBasic.dv_ratio As dv_ratio,
StgDailyBasic.dv_ttm As dv_ttm,
StgDailyBasic.total_share As total_share,
StgDailyBasic.float_share As float_share,
StgDailyBasic.free_share As free_share,
StgDailyBasic.total_mv As total_mv,
StgDailyBasic.circ_mv As circ_mv
From {{ source('finance', 'stg.tushare_daily') }} As StgDaily
Inner Join {{ ref('dw.dim_date') }} As DimDate
On StgDaily.trade_date = Cast(DimDate.date_id As String)
Any Left Join {{ source('finance', 'stg.tushare_daily_basic') }} As StgDailyBasic
On StgDaily.ts_code = StgDailyBasic.ts_code
And StgDaily.trade_date = StgDailyBasic.trade_date
Any Left Join {{ source('finance', 'stg.tushare_adj_factor') }} As StgAdjFactor
On StgDaily.ts_code = StgAdjFactor.ts_code
And StgDaily.trade_date = StgAdjFactor.trade_date
{% if is_incremental() %}
Where StgDaily.trade_date >= (Select cast(max(date_id) As String) From {{ this }})
{% endif %}