資料指標2

Posted on by

一、SQL

  1. 在AdventureWork2012資料庫建立查詢，查詢COLUMNS、INFORMATION_SCHEMA.TABLES所有資料，並將兩段語法分別複製起來。

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.COLUMNS)

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.TABLES)

  1. 建立一個新資料庫，新增檢視表，將剛剛兩段語法分別貼上，執行後，分別儲存成vAdv2012Columns、vAdv2012Tables兩張檢視表。
  2. 建立新檢視表，加入vAdv2012Columns、vAdv2012Tables兩個資料表，用Table_Name做關聯。
  3. 開啟EXCEL，「資料」頁籤→取得外部資料→從SQL Server→伺服器名稱：.、WINDOWS驗證，下一步，選取剛剛建立的檢視表(vAdv2012AllCharColumns)→下一步→完成執行成儲存成vAdv2012AllCharColumns檢視表。
  4. 修改欄位名稱：
  • TABLE_NAME→資料表
  • COLUMN_NAME→欄位名稱
  • IS_NULLABLE→NULL
  • CHARACTER_MAXIMUM_LENGTH→長度
  • 新增一欄：說明

二、統計Product.Color各種字元長度的資料筆數。

  1. 查詢Product資料表中，Color、Name、ProductNumber三個欄位的平均長度。
SELECT ‘Color’ AS 欄位名稱, avg(Len([Color])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

union

SELECT ‘Name’ AS 欄位名稱, avg(Len([Name])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

union

SELECT ‘ProductNumber’ AS 欄位名稱, avg(Len([ProductNumber])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]
  1. 用子查詢(data)的結果，依ColorLength做分組(GROUP BY)，再依ColorLength做排序
SELECT ColorLength, count(*) as 資料筆數

FROM

–名為data的子查詢

(

SELECT TOP 1000 [Name]

,[Color]

,Case When [Color] is Null then 0 else Len([Color]) end ColorLength

FROM [AdventureWorks2012].[Production].[Product]

order by Case When [Color] is Null then 0 else Len([Color]) end

) data

GROUP BY ColorLength

ORDER BY ColorLength

三、查詢AdventureWorks2012資料庫中各個表格的每個欄位中的字元長度

  1. 把vAdv2012AllCharColumns檢視表增加Table_Catalog、vAdv2012Tables.Table_Schema兩個欄位，並移到最上面，IS_NULLABLE欄為增加篩選條件= ‘NO’。
  1. 將http://data.bilab.pro:999/db/Select_Min.txt的語法複製到新的查詢(語法可參考20170309_Cursor)。
    Declare mydata Cursor for

    –因為最後cmd1的指令會超過定義的字串長度，故用top20限制資料來源筆數

    SELECT          top 20 dbo.vAdv2012Tables.TABLE_CATALOG, dbo.vAdv2012Tables.TABLE_SCHEMA, dbo.vAdv2012Columns.TABLE_NAME,

    dbo.vAdv2012Columns.COLUMN_NAME, dbo.vAdv2012Columns.IS_NULLABLE,

    dbo.vAdv2012Columns.CHARACTER_MAXIMUM_LENGTH

    FROM              dbo.vAdv2012Columns INNER JOIN

    dbo.vAdv2012Tables ON dbo.vAdv2012Columns.TABLE_NAME = dbo.vAdv2012Tables.TABLE_NAME

    WHERE          (dbo.vAdv2012Columns.DATA_TYPE = N’nvarchar’) AND (dbo.vAdv2012Tables.TABLE_TYPE = ‘BASE TABLE’) AND

    (dbo.vAdv2012Columns.IS_NULLABLE = ‘NO’)

    declare @cmd1 nvarchar(4000)

    –上面資料來源查詢了6個欄位，所以要宣告6個變數來儲存

    declare @colCatalog nvarchar(100)

    declare @colSchema nvarchar(100)

    declare @colTable nvarchar(100)

    declare @colColumn nvarchar(100)

    declare @colNull nvarchar(100)

    declare @colLen nvarchar(100)

    Open mydata

    Fetch Next from mydata into @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen

    Declare @flag int

    set @flag=0

    set @cmd1=”

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @flag=1

    begin

    set @cmd1=@cmd1 + ‘ union ‘;

    end

    SET @cmd1=@cmd1+’ Select ”’ + @colTable + ”’ as ‘+ N’表格名稱’ + ‘, ”’+@colColumn+ ”’ as ‘+N’欄位名稱’+’, avg(Len(‘ + @colLen + ‘)) as ‘ + N’平均長度’ + ‘ from ‘ + @colCatalog + ‘.’ + @colSchema + ‘.’ + @colTable

    set @flag=1

    FETCH NEXT FROM mydata INTO @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen

    END

    print (@cmd1)

    exec (@cmd1)

    CLOSE mydata

    DEALLOCATE mydata

Leave a Reply