Giải thích và ví dụ về câu lệnh CREATE TABLE AS SELECT (CTAS) trong Synapse SQL để phát triển các giải pháp.

Bạn đang xem : tạo bảng với chọn

TẠO BẢNG NHƯ CHỌN (CTAS)

Trong bài viết này

Bài viết này giải thích câu lệnh T-SQL CREATE TABLE AS SELECT (CTAS) trong Synapse SQL để phát triển các giải pháp. Bài báo cũng cung cấp các ví dụ về mã.

TẠO BẢNG NHƯ CHỌN

Câu lệnh CREATE TABLE AS SELECT (CTAS) là một trong những tính năng T-SQL quan trọng nhất hiện có. CTAS là một hoạt động song song tạo một bảng mới dựa trên kết quả đầu ra của một câu lệnh SELECT. CTAS là cách đơn giản và nhanh nhất để tạo và chèn dữ liệu vào bảng chỉ bằng một lệnh.

CHỌN … VÀO CTAS so với CTAS

CTAS là phiên bản có thể tùy chỉnh nhiều hơn của câu lệnh SELECT … INTO .

Sau đây là ví dụ về lệnh SELECT … INTO đơn giản:

  CHỌN *
VÀO [dbo]. [FactInternetSales_new]
TỪ [dbo]. [FactInternetSales]
 

SELECT … INTO không cho phép bạn thay đổi phương pháp phân phối hoặc loại chỉ mục như một phần của hoạt động. Bạn tạo [dbo]. [FactInternetSales_new] bằng cách sử dụng loại phân phối mặc định của ROUND_ROBIN và cấu trúc bảng mặc định của CLUSTERED COLUMNSTORE INDEX.

Mặt khác, với CTAS, bạn có thể chỉ định cả phân phối dữ liệu bảng cũng như kiểu cấu trúc bảng. Để chuyển đổi ví dụ trước đó thành CTAS:

  TẠO BẢNG [dbo]. [FactInternetSales_new]
VỚI
(
 DISTRIBUTION = ROUND_ROBIN
 , CHỈ SỐ CỘT CỘT ĐƯỢC ĐIỀU CHỈNH
)
BẰNG 
LỰA CHỌN  *
TỪ [dbo]. [FactInternetSales];
 

Lưu ý

Nếu bạn chỉ đang cố gắng thay đổi chỉ mục trong hoạt động CTAS của mình và bảng nguồn được phân phối theo hàm băm, hãy duy trì cùng một cột phân phối và kiểu dữ liệu. Điều này tránh di chuyển dữ liệu phân phối chéo trong quá trình hoạt động, điều này hiệu quả hơn.

Sử dụng CTAS để sao chép bảng

Có lẽ một trong những cách sử dụng phổ biến nhất của CTAS là tạo một bản sao của bảng để thay đổi DDL. Giả sử ban đầu bạn đã tạo bảng của mình dưới dạng ROUND_ROBIN và bây giờ muốn thay đổi nó thành một bảng được phân phối trên một cột. CTAS là cách bạn thay đổi cột phân phối. Bạn cũng có thể sử dụng CTAS để thay đổi loại phân vùng, lập chỉ mục hoặc cột.

Giả sử bạn đã tạo bảng này bằng cách sử dụng loại phân phối mặc định là ROUND_ROBIN , không chỉ định cột phân phối trong TẠO BẢNG .

  TẠO BẢNG FactInternetSales
(
    ProductKey int NOT NULL,
    OrderDateKey int NOT NULL,
    DueDateKey int NOT NULL,
    ShipDateKey int NOT NULL,
    CustomerKey int NOT NULL,
    PromotionKey int NOT NULL,
    CurrencyKey int NOT NULL,
    SalesTerritoryKey int NOT NULL,
    SalesOrderNumber nvarchar (20) KHÔNG ĐỦ,
    SalesOrderLineNumber tí hon KHÔNG ĐẦY,
    RevisionNumber tinyint NOT NULL,
    Số lượng smallint KHÔNG ĐẦY ĐỦ,
    Đơn vị Giá tiền KHÔNG ĐẦY ĐỦ,
    ExtendedAmount tiền KHÔNG ĐẦY ĐỦ,
    UnitPriceDiscountPct float KHÔNG ĐỦ,
    Số tiền giảm giá không phải là NULL,
    Sản phẩm Tiêu chuẩn Chi phí tiền KHÔNG ĐẦY ĐỦ,
    TotalProductCost tiền KHÔNG ĐẦY,
    Số tiền bán hàng KHÔNG ĐẦY ĐỦ,
    TaxAmt money KHÔNG ĐẦY ĐỦ,
    Tiền vận chuyển KHÔNG ĐẦY ĐỦ,
    CarrierTrackingNumber nvarchar (25),
    CustomerPONumber nvarchar (25));
 

Bây giờ bạn muốn tạo một bản sao mới của bảng này, với Chỉ mục Columnstore theo cụm , vì vậy bạn có thể tận dụng hiệu suất của các bảng Columnstore theo cụm. Bạn cũng muốn phân phối bảng này trên ProductKey , vì bạn đang dự đoán các phép nối trên cột này và muốn tránh di chuyển dữ liệu trong quá trình kết hợp trên ProductKey . Cuối cùng, bạn cũng muốn thêm phân vùng trên OrderDateKey , vì vậy bạn có thể nhanh chóng xóa dữ liệu cũ bằng cách loại bỏ các phân vùng cũ. Đây là câu lệnh CTAS, sao chép bảng cũ của bạn vào một bảng mới.

 TẠO BẢNG FactInternetSales_new
VỚI
(
    CHỈ SỐ CỘT CỘT ĐƯỢC ĐIỀU CHỈNH,
    PHÂN PHỐI = HASH (ProductKey),
    VÁCH NGĂN
    (
        OrderDateKey RANGE QUYỀN CHO CÁC GIÁ TRỊ
        (
        20000101,20010101,20020101,20030101,20040101,20050101,20060101,20070101,20080101,20090101,
        20100101,20110101,20120101,20130101,20140101,20150101,20160101,20170101,20180101,20190101,
        20200101,20210101,20220101,20230101,20240101,20250101,20260101,20270101,20280101,20290101
        )
    )
)
NHƯ CHỌN * TỪ FactInternetSales;
 

Cuối cùng, bạn có thể đổi tên các bảng của mình để hoán đổi trong bảng mới và sau đó bỏ bảng cũ.

  RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;
 

Sử dụng CTAS để khắc phục các tính năng không được hỗ trợ

Bạn cũng có thể sử dụng CTAS để giải quyết một số tính năng không được hỗ trợ được liệt kê bên dưới. Phương pháp này thường có thể tỏ ra hữu ích, bởi vì không chỉ mã của bạn sẽ tuân thủ mà còn thường chạy nhanh hơn trên Synapse SQL. Hiệu suất này là kết quả của thiết kế hoàn toàn song song của nó. Các tình huống bao gồm:

  • ANSI THAM GIA vào các CẬP NHẬT
  • ANSI THAM GIA trên XÓA
  • Câu lệnh MERGE
Xem Thêm  Đối tượng là gì trong Java với các ví dụ về lập trình - một đối tượng trong lập trình java là gì

Mẹo

Trước tiên, hãy thử nghĩ “CTAS”. Giải quyết vấn đề bằng cách sử dụng CTAS nói chung là một cách tiếp cận tốt, ngay cả khi kết quả là bạn đang ghi nhiều dữ liệu hơn.

Bạn có thể thấy rằng bạn có một bản cập nhật phức tạp. Bản cập nhật kết hợp nhiều hơn hai bảng với nhau bằng cách sử dụng cú pháp nối ANSI để thực hiện CẬP NHẬT hoặc XÓA.

Hãy tưởng tượng bạn phải cập nhật bảng này:

  TẠO BẢNG [dbo]. [Hàng nămCategorySales]
([EnglishProductCategoryName] NVARCHAR (50) KHÔNG ĐẦY ĐỦ
, [CalendarYear] SMALLINT KHÔNG ĐẦY ĐỦ
, [TotalSalesAmount] TIỀN KHÔNG ĐỦ
)
VỚI
(
    DISTRIBUTION = ROUND_ROBIN
);
 

Truy vấn ban đầu có thể trông giống như ví dụ sau:

  CẬP NHẬT acs
SET [TotalSalesAmount] = [fis]. [TotalSalesAmount]
TỪ [dbo]. [Hàng nămCategorySales] AS acs
THAM GIA    (
        CHỌN [EnglishProductCategoryName]
        , [Năm dương lịch]
        , SUM ([SalesAmount]) AS [TotalSalesAmount]
        TỪ [dbo]. [FactInternetSales] NHƯ
        THAM GIA [dbo]. [DimDate] AS ngày ON s. [OrderDateKey] = d. [DateKey]
        THAM GIA [dbo]. [DimProduct] AS p ON s. [ProductKey] = p. [ProductKey]
        THAM GIA [dbo]. [DimProductSubCategory] AS bạn ON p. [ProductSubcategoryKey] = u. [ProductSubcategoryKey]
        THAM GIA [dbo]. [DimProductCategory] AS c ON u. [ProductCategoryKey] = c. [ProductCategoryKey]
        WHERE [CalendarYear] = 2004
        NHÓM THEO
                [EnglishProductCategoryName]
        ,        [Năm dương lịch]
        ) NHƯ cá
BẬT [acs]. [EnglishProductCategoryName] = [fis]. [EnglishProductCategoryName]
AND [acs]. [CalendarYear] = [fis]. [CalendarYear];
 

Synapse SQL không hỗ trợ tham gia ANSI trong mệnh đề FROM của câu lệnh UPDATE , vì vậy bạn không thể sử dụng ví dụ trước mà không sửa đổi nó.

Bạn có thể sử dụng kết hợp CTAS và kết hợp ngầm định để thay thế ví dụ trước đó:

  - Tạo bảng tạm thời
TẠO BẢNG CTAS_acs
VỚI (PHÂN PHỐI = ROUND_ROBIN)
BẰNG 
CHỌN ISNULL (CAST ([EnglishProductCategoryName] AS NVARCHAR (50)), 0) AS [EnglishProductCategoryName]
, ISNULL (CAST ([CalendarYear] AS SMALLINT), 0) AS [CalendarYear]
, ISNULL (CAST (SUM ([SalesAmount]) AS MONEY), 0) AS [TotalSalesAmount]
TỪ [dbo]. [FactInternetSales] NHƯ
THAM GIA [dbo]. [DimDate] AS ngày ON s. [OrderDateKey] = d. [DateKey]
THAM GIA [dbo]. [DimProduct] AS p ON s. [ProductKey] = p. [ProductKey]
THAM GIA [dbo]. [DimProductSubCategory] AS bạn ON p. [ProductSubcategoryKey] = u. [ProductSubcategoryKey]
THAM GIA [dbo]. [DimProductCategory] AS c ON u. [ProductCategoryKey] = c. [ProductCategoryKey]
WHERE [CalendarYear] = 2004
GROUP BY [EnglishProductCategoryName]
, [Năm dương lịch];

- Sử dụng một phép nối ngầm để thực hiện cập nhật
CẬP NHẬT Hàng nămCategorySales
SET Hàng nămCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
TỪ CTAS_acs
WHERE CTAS_acs. [EnglishProductCategoryName] = Hàng nămCategorySales. [EnglishProductCategoryName]
AND CTAS_acs. [CalendarYear] = Hàng nămCategorySales. [CalendarYear];

- Cắt bảng tạm thời
DROP TABLE CTAS_acs;
 

ANSI tham gia thay thế cho MERGE

Trong Azure Synapse Analytics, MERGE (xem trước) với MỤC TIÊU KHÔNG ĐƯỢC PHÙ HỢP VỚI MỤC TIÊU yêu cầu mục tiêu phải là một bảng được phân phối HASH. Người dùng có thể sử dụng ANSI JOIN với UPDATE hoặc DELETE như một giải pháp thay thế để sửa đổi dữ liệu bảng mục tiêu dựa trên kết quả từ việc kết hợp với một bảng khác. Đây là một ví dụ.

  TẠO BẢNG dbo.Table1
    (ColA INT NOT NULL, ColB DECIMAL (10,3) NOT NULL);
ĐI
TẠO BẢNG dbo.Table2
    (ColA INT NOT NULL, ColB DECIMAL (10,3) NOT NULL);
ĐI
CHÈN VÀO CÁC GIÁ TRỊ dbo.Table1 (1, 10.0);
CHÈN VÀO CÁC GIÁ TRỊ dbo.Table2 (1, 0.0);
ĐI
CẬP NHẬT dbo.Table2
ĐẶT dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
TỪ dbo.Table2
    INNER THAM GIA dbo.Table1
    ON (dbo.Table2.ColA = dbo.Table1.ColA);
ĐI
CHỌN ColA, ColB
TỪ dbo.Table2;

 

Nêu rõ kiểu dữ liệu và khả năng vô hiệu của đầu ra

Khi di chuyển mã, bạn có thể thấy mình gặp phải loại mẫu mã này:

  DECLARE @d decimal (7,2) = 85.455
, @f float (24) = 85.455

KẾT QUẢ TẠO BẢNG
(kết quả DECIMAL (7,2) KHÔNG ĐẦY ĐỦ
)
VỚI (PHÂN PHỐI = ROUND_ROBIN)

CHÈN VÀO kết quả
CHỌN @ d * @ f;
 

Bạn có thể nghĩ rằng bạn nên chuyển mã này sang CTAS, và bạn đã đúng. Tuy nhiên, có một vấn đề ẩn ở đây.

Xem Thêm  list-style-type - CSS: Cascading Style Sheets - kiểu danh sách html

Đoạn mã sau không mang lại kết quả tương tự:

  DECLARE @d decimal (7,2) = 85.455
, @f float (24) = 85.455;

TẠO BẢNG ctas_r
VỚI (PHÂN PHỐI = ROUND_ROBIN)
BẰNG 
Kết quả là SELECT @ d * @ f;
 

Lưu ý rằng cột “kết quả” mang giá trị kiểu dữ liệu và giá trị vô hiệu của biểu thức. Việc chuyển loại dữ liệu về phía trước có thể dẫn đến sự khác biệt nhỏ về giá trị nếu bạn không cẩn thận.

Hãy thử ví dụ sau:

  CHỌN kết quả, kết quả * @ d
từ kết quả;

CHỌN kết quả, kết quả * @ d
từ ctas_r;
 

Giá trị được lưu trữ cho kết quả là khác nhau. Khi giá trị duy trì trong cột kết quả được sử dụng trong các biểu thức khác, lỗi này thậm chí còn trở nên nghiêm trọng hơn.

Điều này quan trọng đối với việc di chuyển dữ liệu. Mặc dù truy vấn thứ hai được cho là chính xác hơn, nhưng có một vấn đề. Dữ liệu sẽ khác so với hệ thống nguồn và điều đó dẫn đến các câu hỏi về tính toàn vẹn trong quá trình di chuyển. Đây là một trong những trường hợp hiếm hoi mà câu trả lời “sai” thực sự là câu trả lời đúng!

Lý do chúng tôi thấy sự chênh lệch giữa hai kết quả là do kiểu truyền ngầm. Trong ví dụ đầu tiên, bảng xác định định nghĩa cột. Khi hàng được chèn vào, một chuyển đổi kiểu ngầm định sẽ xảy ra. Trong ví dụ thứ hai, không có chuyển đổi kiểu ngầm định vì biểu thức xác định kiểu dữ liệu của cột.

Cũng lưu ý rằng cột trong ví dụ thứ hai đã được xác định là cột NULLable, trong khi trong ví dụ đầu tiên thì không. Khi bảng được tạo trong ví dụ đầu tiên, khả năng vô hiệu của cột được xác định rõ ràng. Trong ví dụ thứ hai, nó được để cho biểu thức và theo mặc định sẽ dẫn đến định nghĩa NULL.

Để giải quyết những vấn đề này, bạn phải đặt rõ ràng chuyển đổi kiểu và khả năng vô hiệu trong phần CHỌN của câu lệnh CTAS. Bạn không thể đặt các thuộc tính này trong ‘TẠO BẢNG’.
Ví dụ sau minh họa cách sửa mã:

  DECLARE @d decimal (7,2) = 85.455
, @f float (24) = 85.455

TẠO BẢNG ctas_r
VỚI (PHÂN PHỐI = ROUND_ROBIN)
BẰNG 
Kết quả là CHỌN ISNULL (CAST (@ d * @ f AS DECIMAL (7,2)), 0)
 

Lưu ý những điều sau:

  • Bạn có thể sử dụng CAST hoặc CONVERT.
  • Sử dụng ISNULL, không phải COALESCE, để buộc NULLability. Xem ghi chú sau.
  • ISNULL là chức năng ngoài cùng.
  • Phần thứ hai của ISNULL là một hằng số, 0.

Lưu ý

Để khả năng vô hiệu được đặt chính xác, điều quan trọng là sử dụng ISNULL chứ không phải COALESCE. COALESCE không phải là một hàm xác định, và do đó, kết quả của biểu thức sẽ luôn là NULLable. ISNULL thì khác. Nó mang tính xác định. Do đó, khi phần thứ hai của hàm ISNULL là một hằng số hoặc một ký tự, giá trị kết quả sẽ KHÔNG ĐỦ.

Đảm bảo tính toàn vẹn của các tính toán của bạn cũng rất quan trọng đối với việc chuyển đổi phân vùng bảng. Hãy tưởng tượng bạn có bảng này được định nghĩa là một bảng dữ kiện:

  TẠO BẢNG [dbo]. [Bán hàng]
(
    [date] INT NOT NULL
, [sản phẩm] INT NOT NULL
, [store] INT NOT NULL
, [số lượng] INT NOT NULL
, [giá] TIỀN KHÔNG ĐẦY ĐỦ
, [số tiền] TIỀN KHÔNG ĐẦY ĐỦ
)
VỚI
(PHÂN PHỐI = HASH ([sản phẩm])
, PHẦN ([date] QUYỀN QUYỀN CHO CÁC GIÁ TRỊ
                    (20000101,20010101,20020101
                    , 20030101,20040101,20050101
                    )
                )
);
 

Tuy nhiên, trường số tiền là một biểu thức được tính toán. Nó không phải là một phần của dữ liệu nguồn.

Để tạo tập dữ liệu được phân vùng, bạn có thể muốn sử dụng mã sau:

  TẠO BẢNG [dbo]. [Sales_in]
VỚI
(PHÂN PHỐI = HASH ([sản phẩm])
, PHẦN ([date] QUYỀN QUYỀN CHO CÁC GIÁ TRỊ
                    (20000101,20010101
                    )
                )
)
BẰNG 
LỰA CHỌN
    [ngày]
,   [sản phẩm]
,   [cửa hàng]
,   [số lượng]
,   [giá bán]
, [số lượng] * [giá] NHƯ [số lượng]
TỪ [stg]. [Nguồn]
OPTION (LABEL = 'CTAS: Partition IN table: Create');
 

Truy vấn sẽ chạy hoàn toàn tốt. Sự cố xảy ra khi bạn cố gắng thực hiện chuyển đổi phân vùng. Các định nghĩa bảng không khớp. Để làm cho các định nghĩa bảng khớp với nhau, hãy sửa đổi CTAS để thêm hàm ISNULL để duy trì thuộc tính nullability của cột.

  TẠO BẢNG [dbo]. [Sales_in]
VỚI
(PHÂN PHỐI = HASH ([sản phẩm])
, PHẦN ([date] QUYỀN QUYỀN CHO CÁC GIÁ TRỊ
                    (20000101,20010101
                    )
                )
)
BẰNG 
LỰA CHỌN
  [ngày]
, [sản phẩm]
, [cửa hàng]
, [số lượng]
, [giá bán]
, ISNULL (CAST ([số lượng] * [giá] NHƯ TIỀN), 0) NHƯ [số tiền]
TỪ [stg]. [Nguồn]
OPTION (LABEL = 'CTAS: Partition IN table: Create');
 

Bạn có thể thấy rằng tính nhất quán của kiểu và duy trì thuộc tính vô hiệu trên CTAS là phương pháp kỹ thuật tốt nhất. Nó giúp duy trì tính toàn vẹn trong tính toán của bạn và cũng đảm bảo rằng có thể chuyển đổi phân vùng.

Xem Thêm  Bảng HTML - bảng html chia ô thành hai hàng

CTAS là một trong những câu lệnh quan trọng nhất trong Synapse SQL. Hãy chắc chắn rằng bạn đã hiểu cặn kẽ về nó. Xem tài liệu CTAS .

Các bước tiếp theo

Để biết thêm các mẹo phát triển, hãy xem tổng quan về phát triển .


Xem thêm những thông tin liên quan đến chủ đề tạo bảng với lựa chọn

Thông qua việc lựa chọn người cùng đi phó bản tình duyên và làm nhiệm vụ – Nhat Luong

  • Tác giả: Nhat Luong
  • Ngày đăng: 2022-06-07
  • Đánh giá: 4 ⭐ ( 7268 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Nhat Luong
    Thông qua việc lựa chọn người cùng đi phó bản tình duyên và làm nhiệm vụ

Hướng dẫn cách tạo List Box (danh sách tùy chọn) trong Excel

  • Tác giả: www.ketoan.vn
  • Đánh giá: 4 ⭐ ( 2818 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: List Box (danh sách tùy chọn) được sử dụng để hạn chế số lượng các lựa chọn có sẵn cho người dùng, ngăn cản lỗi chính tả và làm cho dữ liệu nhập nhanh hơn.

Tạo bảng chọn dữ liệu trong Excel

  • Tác giả: www.giaiphapexcel.com
  • Đánh giá: 3 ⭐ ( 9019 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: TẠO BẢNG CHỌN DỮ LIỆU (DATA SELECTOR)

    Bài của Lê văn Duyệt

    Trong các doanh nghiệp nhỏ dữ liệu thường được bố trí trên Excel. Một số chủ doanh nghiệp…

Cách tạo bảng trong Excel 2019 – Hướng dẫn chi tiết cho

  • Tác giả: downvn.net
  • Đánh giá: 4 ⭐ ( 6911 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Cách tạo bảng trong Excel 2019,thủ thuật tin học, downvn.net , download không giới hạn ,Hướng dẫn chi tiết cho người mới bắt đầu

Cách tạo List drop-down chọn dữ liệu trong Excel

  • Tác giả: thuthuatnhanh.com
  • Đánh giá: 4 ⭐ ( 1105 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Hướng dẫn cách tạo List drop-down trong Excel để tự chọn dữ liệu có sẵn và có tính phân loại. Tạo danh sách xổ xuống giúp bạn chọn nhanh dữ liệu trong box.

Cách Tạo Bảng Trong Excel Đơn Giản Nhất

  • Tác giả: tinhocmos.edu.vn
  • Đánh giá: 3 ⭐ ( 6827 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Cách tạo bảng trong excel đơn giản nhất chỉ có tại tinhocmos đơn giản và hiệu quả. Cụ thể, chúng tôi sẽ hướng dẫn 2 cách tạo bảng trong excel 2010, 2013

Cách tạo bảng trong Word và định dạng bảng chi tiết đơn giản nhất

  • Tác giả: www.dienmayxanh.com
  • Đánh giá: 4 ⭐ ( 8176 lượt đánh giá )
  • Khớp với kết quả tìm kiếm: Bảng trong Word là một công cụ hữu ích, giúp bạn trình bày nội dung rõ ràng. Điện máy XANH sẽ hướng dẫn bạn tạo bảng, định dạng và xóa bảng trong Word 2016 chi tiết nhé!

Xem thêm các bài viết khác thuộc chuyên mục: Kiến thức lập trình

By ads_php