二、環境架設與資料表設定
資料庫使用環境
資料庫：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
GOConfig 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'
GOVIEW
建立 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
)
GOSP
建立資料清洗 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
GOPython 環境
開發 Python 使用的 IDE 是 Visual Studio Code，直接在延伸模組安裝 Python 即可。本次 Python 有額外使用第三方 module，需先安裝：
# 安裝 AES 加解密
pip install PyCryptodome
# 使用 DataFrame
pip install pandas
# 連接 SQL Server
pip install pyodbc
# 繪製折線圖
pip install matplotlibLast updated