46 lines
1.4 KiB
MySQL
46 lines
1.4 KiB
MySQL
|
|
||
|
|
/*
|
||
|
|
股票列表
|
||
|
|
*/
|
||
|
|
{{
|
||
|
|
config(
|
||
|
|
materialized='table',
|
||
|
|
engine='MergeTree',
|
||
|
|
order_by='ts_code'
|
||
|
|
)
|
||
|
|
}}
|
||
|
|
SELECT
|
||
|
|
StgStock.ts_code As ts_code
|
||
|
|
, DimExchange.exchange_id As exchange_id
|
||
|
|
, arrayElement(splitByChar('.', StgStock.`ts_code`),1) As code
|
||
|
|
, StgStock.name As name
|
||
|
|
, StgStock.fullname As full_name
|
||
|
|
, StgStock.enname As name_en
|
||
|
|
, StgStock.cnspell As cn_spell
|
||
|
|
, StgStock.area As area
|
||
|
|
, StgStock.industry As industry
|
||
|
|
, StgStock.market As market
|
||
|
|
, StgStock.curr_type As currency
|
||
|
|
, ListDate.full_date As list_date
|
||
|
|
, Case When StgStock.delist_date Is Null Or StgStock.delist_date = '' Then Null Else DelistDate.full_date End As delist_date
|
||
|
|
, Case
|
||
|
|
When StgStock.list_status = 'L'
|
||
|
|
Then 1
|
||
|
|
Else 0
|
||
|
|
End As is_active
|
||
|
|
, Case
|
||
|
|
When is_hs In ('H', 'S')
|
||
|
|
Then 1
|
||
|
|
Else 0
|
||
|
|
End As is_hongkong_connect
|
||
|
|
, act_name As actual_controller
|
||
|
|
, act_ent_type As actual_controller_enterprise_type
|
||
|
|
, now() As dt
|
||
|
|
From {{ source('finance', 'stg.tushare_stock_basic') }} As StgStock Final
|
||
|
|
Left Join {{ ref("dw.dim_exchange") }} As DimExchange
|
||
|
|
On StgStock.exchange = DimExchange.ts_exchange_code
|
||
|
|
Left Join {{ ref("dw.dim_date") }} As ListDate
|
||
|
|
On StgStock.list_date = Cast(ListDate.date_id As String)
|
||
|
|
Left Join {{ ref("dw.dim_date") }} As DelistDate
|
||
|
|
On StgStock.delist_date = Cast(DelistDate.date_id As String)
|