二、環境架設與資料表設定

資料庫使用環境

• 資料庫：SQL Server 2022 Developer

• Python：Python 3.11.3

• 玉山富果 API 文件：https://developer.fugle.tw/docs/data/intro

資料庫環境

資料庫

建立資料庫

USE [master]

CREATE DATABASE [Stock_API]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Stock_API', FILENAME = N'資料庫放置資料夾\Stock_API.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Stock_API_log', FILENAME = N'資料庫放置資料夾\Stock_API_log.ldf' , SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 COLLATE Chinese_Taiwan_Stroke_CI_AS
 WITH CATALOG_COLLATION = DATABASE_DEFAULT, LEDGER = OFF

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Stock_API].[dbo].[sp_fulltext_database] @action = 'enable'
end

ALTER DATABASE [Stock_API] SET ANSI_NULL_DEFAULT OFF 

ALTER DATABASE [Stock_API] SET ANSI_NULLS OFF 

ALTER DATABASE [Stock_API] SET ANSI_PADDING OFF 

ALTER DATABASE [Stock_API] SET ANSI_WARNINGS OFF 

ALTER DATABASE [Stock_API] SET ARITHABORT OFF 

ALTER DATABASE [Stock_API] SET AUTO_CLOSE OFF 

ALTER DATABASE [Stock_API] SET AUTO_SHRINK OFF 

ALTER DATABASE [Stock_API] SET AUTO_UPDATE_STATISTICS ON 

ALTER DATABASE [Stock_API] SET CURSOR_CLOSE_ON_COMMIT OFF 

ALTER DATABASE [Stock_API] SET CURSOR_DEFAULT  GLOBAL 

ALTER DATABASE [Stock_API] SET CONCAT_NULL_YIELDS_NULL OFF 

ALTER DATABASE [Stock_API] SET NUMERIC_ROUNDABORT OFF 

ALTER DATABASE [Stock_API] SET QUOTED_IDENTIFIER OFF 

ALTER DATABASE [Stock_API] SET RECURSIVE_TRIGGERS OFF 

ALTER DATABASE [Stock_API] SET  DISABLE_BROKER 

ALTER DATABASE [Stock_API] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

ALTER DATABASE [Stock_API] SET DATE_CORRELATION_OPTIMIZATION OFF 

ALTER DATABASE [Stock_API] SET TRUSTWORTHY OFF 

ALTER DATABASE [Stock_API] SET ALLOW_SNAPSHOT_ISOLATION OFF 

ALTER DATABASE [Stock_API] SET PARAMETERIZATION SIMPLE 

ALTER DATABASE [Stock_API] SET READ_COMMITTED_SNAPSHOT OFF 

ALTER DATABASE [Stock_API] SET HONOR_BROKER_PRIORITY OFF 

ALTER DATABASE [Stock_API] SET RECOVERY FULL 

ALTER DATABASE [Stock_API] SET  MULTI_USER 

ALTER DATABASE [Stock_API] SET PAGE_VERIFY CHECKSUM  

ALTER DATABASE [Stock_API] SET DB_CHAINING OFF 

ALTER DATABASE [Stock_API] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 

ALTER DATABASE [Stock_API] SET TARGET_RECOVERY_TIME = 60 SECONDS 

ALTER DATABASE [Stock_API] SET DELAYED_DURABILITY = DISABLED 

ALTER DATABASE [Stock_API] SET ACCELERATED_DATABASE_RECOVERY = OFF  

ALTER DATABASE [Stock_API] SET QUERY_STORE = OFF

ALTER DATABASE [Stock_API] SET  READ_WRITE 


GO

Config Table

建立股票營業日－Stock_WorkDay

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_WorkDay](
	[Date]      [date] NOT NULL,
	[Week]      [nvarchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isWorkDay] [varchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Desc]      [nvarchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
 CONSTRAINT [PK_Stock_WorkDay] PRIMARY KEY CLUSTERED 
(
	[Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_WorkDay', @level2type=N'COLUMN',@level2name=N'Date'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'星期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_WorkDay', @level2type=N'COLUMN',@level2name=N'Week'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否為營業日' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_WorkDay', @level2type=N'COLUMN',@level2name=N'isWorkDay'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'備註' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_WorkDay', @level2type=N'COLUMN',@level2name=N'Desc'


GO

建立追蹤股票列表－Stock_Get

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_Get](
	[stock_id]   [varchar](20) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[stock_name] [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isGet]      [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
 CONSTRAINT [PK_Stock_Get] PRIMARY KEY CLUSTERED 
(
	[stock_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Get', @level2type=N'COLUMN',@level2name=N'stock_id'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票名稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Get', @level2type=N'COLUMN',@level2name=N'stock_name'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否追蹤' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Get', @level2type=N'COLUMN',@level2name=N'isGet'


GO

建立各 Table 狀況清單－Stock_Table_Status

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_Table_Status](
	[Table_Name]      [varchar](50) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[Status]          [varchar](1) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[Update_Datetime] [datetime] NULL,
 CONSTRAINT [PK_Stock_Table_Status] PRIMARY KEY CLUSTERED 
(
	[Table_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Table Name' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Table_Status', @level2type=N'COLUMN',@level2name=N'Table_Name'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'狀態（Y = 準備完成，N = 尚未準備完成）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Table_Status', @level2type=N'COLUMN',@level2name=N'Status'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Table_Status', @level2type=N'COLUMN',@level2name=N'Update_Datetime'


GO

股票資訊相關 Table

建立個股基本資訊資料表－Stock_Meta_Stage

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_Meta_Stage](
	[PKEY]                   [int] IDENTITY(1,1) NOT NULL,
	[symbolId]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[date]                   [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[type]                   [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[exchange]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[market]                 [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[countryCode]            [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[timeZone]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[lastUpdatedAt]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[nameZhTw]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[industryZhTw]           [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[previousClose]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceReference]         [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceHighLimit]         [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceLowLimit]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canDayBuySell]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canDaySellBuy]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canShortMargin]         [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canShortLend]           [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[tradingUnit]            [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[currency]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isTerminated]           [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isSuspended]            [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[typeZhTw]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[abnormal]               [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isUnusuallyRecommended] [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isNewlyCompiled]        [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[api_datetime]           [datetime] NULL
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'symbolId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料所屬日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'date'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ticker 類別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'type'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易所' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'exchange'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'市場別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'market'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬國家ISO2代碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'countryCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬時區' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'timeZone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料最後更新時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'lastUpdatedAt'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票中文簡稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'nameZhTw'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬產業別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'industryZhTw'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'昨日收盤價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'previousClose'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'今日參考價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'priceReference'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'漲停價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'priceHighLimit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'跌停價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'priceLowLimit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可先買後賣現股當沖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'canDayBuySell'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可先賣後買現股當沖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'canDaySellBuy'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否豁免平盤下融券賣出' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'canShortMargin'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否豁免平盤下借券賣出' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'canShortLend'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易單位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'tradingUnit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易幣別代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'currency'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'今日是否已終止上市' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'isTerminated'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'今日是否暫停買賣' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'isSuspended'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票類別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'typeZhTw'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'警示或處置股標示 (正常、注意、處置、注意及處置、再次處置、注意及再次處置、彈性處置、注意及彈性處置)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'abnormal'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否為投資理財節目異常推介個股' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'isUnusuallyRecommended'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否為新編指數 (僅指數類別)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta_Stage', @level2type=N'COLUMN',@level2name=N'isNewlyCompiled'


GO

建立個股基本資訊資料表－Stock_Meta

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_Meta](
	[symbolId]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[date]                   [date] NOT NULL,
	[type]                   [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[exchange]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[market]                 [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[countryCode]            [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[timeZone]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[lastUpdatedAt]          [datetime] NULL,
	[nameZhTw]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[industryZhTw]           [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[previousClose]          [numeric](18, 2) NULL,
	[priceReference]         [numeric](18, 2) NULL,
	[priceHighLimit]         [numeric](18, 2) NULL,
	[priceLowLimit]          [numeric](18, 2) NULL,
	[canDayBuySell]          [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canDaySellBuy]          [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canShortMargin]         [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[canShortLend]           [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[tradingUnit]            [int] NULL,
	[currency]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isTerminated]           [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isSuspended]            [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[typeZhTw]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[abnormal]               [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isUnusuallyRecommended] [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[isNewlyCompiled]        [varchar](5) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[ETL_datetime]           [datetime] NULL,
 CONSTRAINT [PK_Stock_Meta] PRIMARY KEY CLUSTERED 
(
	[symbolId] ASC,
	[date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'symbolId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料所屬日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'date'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ticker 類別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'type'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易所' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'exchange'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'市場別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'market'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬國家ISO2代碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'countryCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬時區' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'timeZone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料最後更新時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'lastUpdatedAt'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票中文簡稱' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'nameZhTw'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬產業別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'industryZhTw'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'昨日收盤價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'previousClose'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'今日參考價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'priceReference'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'漲停價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'priceHighLimit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'跌停價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'priceLowLimit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可先買後賣現股當沖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'canDayBuySell'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否可先賣後買現股當沖' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'canDaySellBuy'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否豁免平盤下融券賣出' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'canShortMargin'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否豁免平盤下借券賣出' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'canShortLend'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易單位' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'tradingUnit'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易幣別代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'currency'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'今日是否已終止上市' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'isTerminated'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'今日是否暫停買賣' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'isSuspended'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票類別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'typeZhTw'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'警示或處置股標示 (正常、注意、處置、注意及處置、再次處置、注意及再次處置、彈性處置、注意及彈性處置)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'abnormal'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否為投資理財節目異常推介個股' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'isUnusuallyRecommended'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否為新編指數 (僅指數類別)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Meta', @level2type=N'COLUMN',@level2name=N'isNewlyCompiled'


GO

建立個股交易狀況資料表－Stock_Quote_Stage

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_Quote_Stage](
	[PKEY]            [int] IDENTITY(1,1) NOT NULL,
	[symbolId]        [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[date]            [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[type]            [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[exchange]        [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[market]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[countryCode]     [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[timeZone]        [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[lastUpdatedAt]   [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[trade_at]        [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[trade_bid]       [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[trade_ask]       [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[trade_price]     [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[trade_volume]    [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceHigh_at]    [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceHigh_price] [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceLow_at]     [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceLow_price]  [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceOpen_at]    [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceOpen_price] [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceAvg_at]     [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[priceAvg_price]  [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[change]          [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[changePercent]   [nvarchar](300) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[api_datetime]    [datetime] NULL,
 CONSTRAINT [PK_Stock_Quote_Stage] PRIMARY KEY CLUSTERED 
(
	[PKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'symbolId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料所屬日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'date'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ticker 類別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'type'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易所' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'exchange'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'市場別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'market'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬國家ISO2代碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'countryCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬時區' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'timeZone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料最後更新時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'lastUpdatedAt'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'trade_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交買進價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'trade_bid'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交賣出價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'trade_ask'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'trade_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'trade_volume'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第一次到達當日最高價之時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceHigh_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之最高價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceHigh_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第一次到達當日最低價之時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceLow_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之最低價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceLow_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日第一筆成交時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceOpen_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之開盤價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceOpen_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日最後一筆成交時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceAvg_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之成交均價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'priceAvg_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日股價之漲跌' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'change'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日股價之漲跌幅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote_Stage', @level2type=N'COLUMN',@level2name=N'changePercent'


GO

建立個股交易狀況資料表－Stock_Quote

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Stock_Quote](
	[symbolId]        [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL,
	[date]            [date] NOT NULL,
	[type]            [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[exchange]        [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[market]          [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[countryCode]     [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[timeZone]        [varchar](100) COLLATE Chinese_Taiwan_Stroke_CI_AS NULL,
	[lastUpdatedAt]   [datetime] NULL,
	[trade_at]        [datetime] NULL,
	[trade_bid]       [numeric](18, 2) NULL,
	[trade_ask]       [numeric](18, 2) NULL,
	[trade_price]     [numeric](18, 2) NULL,
	[trade_volume]    [numeric](18, 2) NULL,
	[priceHigh_at]    [datetime] NULL,
	[priceHigh_price] [numeric](18, 2) NULL,
	[priceLow_at]     [datetime] NULL,
	[priceLow_price]  [numeric](18, 2) NULL,
	[priceOpen_at]    [datetime] NULL,
	[priceOpen_price] [numeric](18, 2) NULL,
	[priceAvg_at]     [datetime] NULL,
	[priceAvg_price]  [numeric](18, 2) NULL,
	[change]          [numeric](18, 2) NULL,
	[changePercent]   [numeric](18, 2) NULL,
	[ETL_datetime]    [datetime] NULL,
 CONSTRAINT [PK_Stock_Quote_1] PRIMARY KEY CLUSTERED 
(
	[symbolId] ASC,
	[date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票代號' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'symbolId'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料所屬日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'date'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ticker 類別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'type'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'交易所' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'exchange'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'市場別' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'market'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬國家ISO2代碼' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'countryCode'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'股票所屬時區' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'timeZone'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'本筆資料最後更新時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'lastUpdatedAt'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'trade_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交買進價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'trade_bid'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交賣出價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'trade_ask'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'trade_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最新一筆成交量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'trade_volume'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第一次到達當日最高價之時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceHigh_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之最高價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceHigh_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'第一次到達當日最低價之時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceLow_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之最低價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceLow_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日第一筆成交時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceOpen_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之開盤價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceOpen_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日最後一筆成交時間' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceAvg_at'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日之成交均價' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'priceAvg_price'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日股價之漲跌' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'change'

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'當日股價之漲跌幅' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Stock_Quote', @level2type=N'COLUMN',@level2name=N'changePercent'


GO

VIEW

建立 Python 繪圖資料來源

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON


CREATE VIEW [dbo].[vw_For_Line_Notify] AS
(
 SELECT  Meta.symbolId         AS symbolId       --股票代號
        ,Meta.date             AS FullDate       --年度
        ,RIGHT(Meta.date, 5)   AS date           --日期
        ,Meta.nameZhTw         AS nameZhTw       --股票中文簡稱
        ,Meta.previousClose    AS previousClose  --昨日收盤價
        ,Quote.priceOpen_price AS priceOpen      --當日之開盤價，開盤定義：當天第一筆成交時才開盤
        ,Quote.priceHigh_price AS priceHigh      --當日最高價
        ,Quote.priceLow_price  AS priceLow       --當日最低價
        ,NULL                  AS priceClose     --當日收盤價
        ,Quote.change          AS change         --當日股價漲跌
        ,Quote.changePercent   AS changePercent  --當日股價漲跌幅
        ,'Today'               AS SRC            --來源
 FROM [Stock_API].[dbo].[Stock_Meta] AS Meta
 INNER JOIN [Stock_API].[dbo].[Stock_Quote] AS Quote ON Meta.symbolId = Quote.symbolId AND Meta.date = Quote.date
)

GO

SP

建立資料清洗 SP

USE [Stock_API]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON




CREATE PROCEDURE [dbo].[usp_MERGE_STG_PRESTAGE] @Table_Name VARCHAR(50), @Date DATE
AS

/*
==========================================================
ToDo: 使用 Merge 更新 prestage Table to Stage Table
----------------------------------------------------------
Author      Date         Description
----------------------------------------------------------
Skylar      2023-04-24   新增第一版
==========================================================
*/

--測試用
--DECLARE @TABLE_NAME VARCHAR(MAX) = 'Stock_Meta', @DATE DATE = '2023-05-19'

IF @Table_Name = 'Stock_Meta'
    BEGIN
        --刪除當日舊資料
        DELETE FROM [Stock_API].[dbo].[Stock_Meta] WHERE date = @Date
        --新增當日新資料
        INSERT INTO [Stock_API].[dbo].[Stock_Meta]
        SELECT  CAST(CASE WHEN symbolId               = 'None' THEN NULL ELSE symbolId END AS varchar(100))
               ,CAST(CASE WHEN date                   = 'None' THEN NULL ELSE date END AS date)
               ,CAST(CASE WHEN type                   = 'None' THEN NULL ELSE type END AS varchar(100))
               ,CAST(CASE WHEN exchange               = 'None' THEN NULL ELSE exchange END AS varchar(100))
               ,CAST(CASE WHEN market                 = 'None' THEN NULL ELSE market END AS varchar(100))
               ,CAST(CASE WHEN countryCode            = 'None' THEN NULL ELSE countryCode END AS varchar(100))
               ,CAST(CASE WHEN timeZone               = 'None' THEN NULL ELSE timeZone END AS varchar(100))
               ,CAST(CASE WHEN lastUpdatedAt          = 'None' THEN NULL ELSE CONVERT(datetimeoffset, lastUpdatedAt, 127) END AS DATETIME)
               ,CAST(CASE WHEN nameZhTw               = 'None' THEN NULL ELSE nameZhTw END AS varchar(100))
               ,CAST(CASE WHEN industryZhTw           = 'None' THEN NULL ELSE industryZhTw END AS varchar(100))
               ,CAST(CASE WHEN previousClose          = 'None' THEN NULL ELSE previousClose END AS numeric(18,2))
               ,CAST(CASE WHEN priceReference         = 'None' THEN NULL ELSE priceReference END AS numeric(18,2))
               ,CAST(CASE WHEN priceHighLimit         = 'None' THEN NULL ELSE priceHighLimit END AS numeric(18,2))
               ,CAST(CASE WHEN priceLowLimit          = 'None' THEN NULL ELSE priceLowLimit END AS numeric(18,2))
               ,CAST(CASE WHEN canDayBuySell          = 'None' THEN NULL ELSE canDayBuySell END AS varchar(5))
               ,CAST(CASE WHEN canDaySellBuy          = 'None' THEN NULL ELSE canDaySellBuy END AS varchar(5))
               ,CAST(CASE WHEN canShortMargin         = 'None' THEN NULL ELSE canShortMargin END AS varchar(5))
               ,CAST(CASE WHEN canShortLend           = 'None' THEN NULL ELSE canShortLend END AS varchar(5))
               ,CAST(CASE WHEN tradingUnit            = 'None' THEN NULL ELSE tradingUnit END AS int)
               ,CAST(CASE WHEN currency               = 'None' THEN NULL ELSE currency END AS varchar(100))
               ,CAST(CASE WHEN isTerminated           = 'None' THEN NULL ELSE isTerminated END AS varchar(5))
               ,CAST(CASE WHEN isSuspended            = 'None' THEN NULL ELSE isSuspended END AS varchar(5))
               ,CAST(CASE WHEN typeZhTw               = 'None' THEN NULL ELSE typeZhTw END AS varchar(100))
               ,CAST(CASE WHEN abnormal               = 'None' THEN NULL ELSE abnormal END AS varchar(100))
               ,CAST(CASE WHEN isUnusuallyRecommended = 'None' THEN NULL ELSE isUnusuallyRecommended END AS varchar(5))
               ,CAST(CASE WHEN isNewlyCompiled        = 'None' THEN NULL ELSE isNewlyCompiled END AS varchar(5))
               ,GETDATE()
        FROM [Stock_API].[dbo].[Stock_Meta_Stage]
    END
ELSE IF @Table_Name = 'Stock_Quote'
    BEGIN
        --刪除當日舊資料
        DELETE FROM [Stock_API].[dbo].[Stock_Quote] WHERE date = @Date
        --新增當日新資料
        INSERT INTO [Stock_API].[dbo].[Stock_Quote]
        SELECT  CAST(CASE WHEN symbolId        = 'None' THEN NULL ELSE symbolId END AS varchar(100))
               ,CAST(CASE WHEN date            = 'None' THEN NULL ELSE date END AS date)
               ,CAST(CASE WHEN type            = 'None' THEN NULL ELSE type END AS varchar(100))
               ,CAST(CASE WHEN exchange        = 'None' THEN NULL ELSE exchange END AS varchar(100))
               ,CAST(CASE WHEN market          = 'None' THEN NULL ELSE market END AS varchar(100))
               ,CAST(CASE WHEN countryCode     = 'None' THEN NULL ELSE countryCode END AS varchar(100))
               ,CAST(CASE WHEN timeZone        = 'None' THEN NULL ELSE timeZone END AS varchar(100))
               ,CAST(CASE WHEN lastUpdatedAt   = 'None' THEN NULL ELSE CONVERT(datetimeoffset, lastUpdatedAt, 127) END AS DATETIME)
               ,CAST(CASE WHEN trade_at        = 'None' THEN NULL ELSE CONVERT(datetimeoffset, trade_at, 127) END AS DATETIME)
               ,CAST(CASE WHEN trade_bid       = 'None' THEN NULL ELSE trade_bid END AS numeric(18,2))
               ,CAST(CASE WHEN trade_ask       = 'None' THEN NULL ELSE trade_ask END AS numeric(18,2))
               ,CAST(CASE WHEN trade_price     = 'None' THEN NULL ELSE trade_price END AS numeric(18,2))
               ,CAST(CASE WHEN trade_volume    = 'None' THEN NULL ELSE trade_volume END AS numeric(18,2))
               ,CAST(CASE WHEN priceHigh_at    = 'None' THEN NULL ELSE CONVERT(datetimeoffset, priceHigh_at, 127) END AS DATETIME)
               ,CAST(CASE WHEN priceHigh_price = 'None' THEN NULL ELSE priceHigh_price END AS numeric(18,2))
               ,CAST(CASE WHEN priceLow_at     = 'None' THEN NULL ELSE CONVERT(datetimeoffset, priceLow_at, 127) END AS DATETIME)
               ,CAST(CASE WHEN priceLow_price  = 'None' THEN NULL ELSE priceLow_price END AS numeric(18,2))
               ,CAST(CASE WHEN priceOpen_at    = 'None' THEN NULL ELSE CONVERT(datetimeoffset, priceOpen_at,127) END AS DATETIME)
               ,CAST(CASE WHEN priceOpen_price = 'None' THEN NULL ELSE priceOpen_price END AS numeric(18,2))
               ,CAST(CASE WHEN priceAvg_at     = 'None' THEN NULL ELSE CONVERT(datetimeoffset, priceAvg_at, 127) END AS DATETIME)
               ,CAST(CASE WHEN priceAvg_price  = 'None' THEN NULL ELSE priceAvg_price END AS numeric(18,2))
               ,CAST(CASE WHEN change          = 'None' THEN NULL ELSE change END AS numeric)
               ,CAST(CASE WHEN changePercent   = 'None' THEN NULL ELSE changePercent END AS numeric(18,2))
               ,GETDATE()
        FROM [Stock_API].[dbo].[Stock_Quote_Stage]
    END
ELSE IF @Table_Name = 'Stock_Candles'
    BEGIN
        --刪除當日舊資料
        --DELETE FROM [Stock_API].[dbo].[Stock_Candles] WHERE date = @Date
        --新增當日新資料
        INSERT INTO [Stock_API].[dbo].[Stock_Candles]
        SELECT  CAST(CASE WHEN symbolId = 'None' THEN NULL ELSE symbolId END AS varchar(100))
               ,CAST(CASE WHEN date     = 'None' THEN NULL ELSE date END AS date)
               ,CAST(CASE WHEN type     = 'None' THEN NULL ELSE type END AS varchar(100))
               ,CAST(CASE WHEN exchange = 'None' THEN NULL ELSE exchange END AS varchar(100))
               ,CAST(CASE WHEN market   = 'None' THEN NULL ELSE market END AS varchar(100))
               ,CAST(CASE WHEN [open]   = 'None' THEN NULL ELSE [open] END AS numeric(18,2))
               ,CAST(CASE WHEN high     = 'None' THEN NULL ELSE high END AS numeric(18,2))
               ,CAST(CASE WHEN low      = 'None' THEN NULL ELSE low END AS numeric(18,2))
               ,CAST(CASE WHEN [close]  = 'None' THEN NULL ELSE [close] END AS numeric(18,2))
               ,CAST(CASE WHEN volume   = 'None' THEN NULL ELSE volume END AS numeric(18,2))
               ,CAST(CASE WHEN turnover = 'None' THEN NULL ELSE turnover END AS numeric(18,2))
               ,CAST(CASE WHEN change   = 'None' THEN NULL ELSE change END AS numeric)
               ,GETDATE()
        FROM [Stock_API].[dbo].[Stock_Candles_Stage]
    END


GO

Python 環境

開發 Python 使用的 IDE 是 Visual Studio Code，直接在延伸模組安裝 Python 即可。本次 Python 有額外使用第三方 module，需先安裝：

# 安裝 AES 加解密
pip install PyCryptodome

# 使用 DataFrame
pip install pandas

# 連接 SQL Server
pip install pyodbc

# 繪製折線圖
pip install matplotlib