while-loop - 如何优化查询以使用 SQL ServerV2014 创建具有动态查询的多表

Question

我正在使用 SQL serverV2014。

我有两张桌子：

1. 类别 - 26 行 固定
2. Web - 包含每日数据的大型数据集

架构：

     /* Table-1*/ 
CREATE TABLE Category
(
  ParentCategory VARCHAR(50) NOT NULL,
  CategoryMapped VARCHAR(50) NOT NULL
);
  INSERT INTO Category Values
    ('Infectious Disease','Infectious Disease')
    ,('Genetics','Genetics')
    ,('Recent Developments in','Other')
    ,('Surveys','Content')
    ,('Pain Management','Orthopedics')
    ,('Psychiatry','Psychiatry')
    ,('Endocrinology','Endocrinology')
    ,('Surgery','Surgery')
    ,('Nursing','Other')
    ,('Gastroenterology','Gastroenterology')
    ,('Case Studies','Other')
    ,('Mastery of Medicine','Other')
    ,('Anesthesiology','Anesthesiology')
    ,('Oncology','Oncology/Hematology')
    ,('Blogs','Content')
    ,('Pediatrics','Pediatrics')
    ,('IDSA','Other')
    ,('Special Reports','Other')
    ,('Clinical Connection','Clinical Context')
    ,('Opinion','Other')
    ,('Dermatology','Dermatology')
    ,('Orthopedics','Orthopedics')
    ,('Urology','Urology')
    ,('HIV/AIDS','Primary Care')
    ,('Endocrine Society','Endocrinology')
    ,('OncMadness','Other')
    ,('TedMed','Other')
    ,('ASCO','Oncology/Hematology')
    ,('Cardiology','Cardiology')
    ,('Practice Management','Primary Care')
    ,('Building the Patient-Centered Medical Home','Other')
    ,('Pulmonology','Pulmonology')
    ,('Rheumatology','Rheumatology')
    ,('Emergency Medicine','Primary Care')
    ,('Clinical Challenges','Clinical Context')
    ,('Primary Care','Primary Care')
    ,('Transplantation','Transplantation')
    ,('Sports Medicine','Primary Care')
    ,('Oncology/Hematology','Oncology/Hematology')
    ,('Washington Watch','Other')
    ,('Meeting Coverage','Other')
    ,('Podcasts','Content')
    ,('Hospital-Based Medicine','Primary Care')
    ,('American College of Rheumatology','Rheumatology')
    ,('AAD','Dermatology')
    ,('Allergy & Immunology','Allergy & Immunology')
    ,('Geriatrics','Primary Care')
    ,('Neurology','Neurology')
    ,('Ophthalmology','Ophthalmology')
    ,('Pathology','Primary Care')
    ,('Critical Care','Primary Care')
    ,('Radiology','Radiology')
    ,('Public Health & Policy','Other')
    ,('Washington-Watch','Other')
    ,('Nephrology','Nephrology')
    ,('OB/Gyn','OB/Gyn')
    ,('Innovations in Medicine','Other')
    ,('AGA','Gastroenterology')
    ,('Clinical Focus','Clinical Context')
    ,('Quizzes','Other')
    ;
  /*Table-2 */
 
 Create table Web(
   MUID VARCHAR(10) NOT NULL,
   GA_TBID VARCHAR(10) NOT NULL, 
   EVENTDTTM DATETIME,
   ParentCategory VARCHAR(100) NOT NULL,
   DashboardSpecialty VARCHAR(100) NOT NULL
   );
   INSERT INTO Web Values

('6480030','80387','2019-01-01 09:18:05.000','Meeting Coverage','Ophthalmology')
,('6480873','80383','2019-01-11 19:26:26.000','Infectious Disease','Primary Care')
,('6480873','80388','2019-01-15 09:37:32.000','Neurology','Primary Care')
,('6480873','80383','2019-01-11 11:40:57.000','Infectious Disease','Primary Care')
,('6480873','80388','2019-01-12 09:37:35.000','Neurology','Primary Care')
,('6480873','80383','2019-01-12 11:38:51.000','Infectious Disease','Primary Care')
,('6480873','80388','2019-01-12 09:40:38.000','Neurology','Primary Care')
,('6480873','80410','2019-01-12 23:04:14.000','Public Health & Policy','Primary Care')
,('6480873','80410','2019-01-13 23:04:14.000','Public Health & Policy','Primary Care')
,('6480873','80383','2019-01-15 19:26:26.000','Infectious Disease','Primary Care')
,('6480873','80383','2019-01-11 11:40:02.000','Infectious Disease','Primary Care')
,('6480873','80383','2019-01-01 19:27:06.000','Infectious Disease','Primary Care')
,('6480873','80410','2019-01-15 23:04:53.000','Public Health & Policy','Primary Care')
,('6481093','80315','2019-01-12 16:53:03.000','Public Health & Policy','Primary Care')
,('6481093','80315','2019-01-12 08:04:44.000','Public Health & Policy','Primary Care')
,('6481482','80387','2019-01-12 09:01:58.000','Meeting Coverage','Ophthalmology')
,('6481482','80405','2019-01-12 09:00:58.000','Meeting Coverage','Ophthalmology')
,('6481812','80396','2019-01-12 23:18:28.000','Public Health & Policy','Cardiology')
,('6482049','80419','2019-01-15 19:53:57.000','Public Health & Policy','Obstetrics and Gynecology')
,('6482078','80388','2019-01-15 11:11:56.000','Neurology','Cardiology')
,('6482174','76553','2019-01-15 16:00:12.000','Opinion','Obstetrics and Gynecology')
,('6482215','80410','2019-01-15 13:50:40.000','Public Health & Policy','Primary Care')
,('6482215','80410','2019-01-11 13:52:58.000','Public Health & Policy','Primary Care')
,('6482387','80388','2019-01-11 21:44:29.000','Neurology','Dermatology')
,('6485034','79020','2019-01-11 10:22:29.000','Public Health & Policy','Primary Care')
,('6485114','80396','2019-01-11 21:02:26.000','Public Health & Policy','Primary Care')
,('6485114','80277','2019-01-12 21:26:47.000','Clinical Challenges','Primary Care')
,('6485114','79730','2019-01-12 21:28:47.000','Nursing','Primary Care')
,('6485114','80396','2019-01-12 21:02:21.000','Public Health & Policy','Primary Care')
,('6485114','79717','2019-01-12 21:28:42.000','Public Health & Policy','Primary Care');

我想循环我的查询以计算从 2019 年到本周的一周的简单数据聚合（用户/周/每个类别） ，并且应该将这些数据移动到一个表中。

我的代码：

    DECLARE @StartDate AS DATETIME
    DECLARE @EndDate AS DATETIME
    DECLARE @CurrentDate AS DATETIME

    SET @StartDate = '2019-01-01'
    SET @EndDate = GETDATE()
    SET @CurrentDate = @StartDate

    WHILE (@CurrentDate < @EndDate)
    BEGIN
        IF @@ROWCOUNT < 1
            --print @CurrentDate
            /* Actual Work after every 7-days  */
            
            IF (cast(DATEPART(DAY,@CurrentDate) as INT) % 7 = 0)
                BEGIN
                    Declare 
                    @Week VARCHAR(10) = cast(DATEPART(WEEK,@CurrentDate) as varchar ) ,  
                    @Year VARCHAR(10) = cast(DATEPART(YEAR,@CurrentDate) as varchar);
                    Declare
                    @Col_value  VARCHAR(10) =  'W_' +  @Week + '_'+ @Year;
                    print(  cast(@CurrentDate as varchar) + '  &&&  ' +  @Col_value)

                    ---Aggregating data and inserting into Tabel
                    
                        Declare @query NVARCHAR(MAX) ='
                        select b.CategoryMapped as Category,
                        IFNULL(f.Users,0) as Users,  ' + @Col_value + ' as WeekName
                        into + ' @Col_value ' +   --inserting data into table
                        from Category b 
                        Left join  ---second join to get 26-rows from category
                          (
                            select b.CategoryMapped as Category,
                              count(Distinct a.MUID) as Users
                            from web a
                            left join category  b     ---first join to get aggregate data from web w.r.t category
                            on b.ParentCategory =a.ParentCategory
                            where DATEPART(YEAR,a.EventDTTM) =  + ' @Year ' + and YEAR(WEEK,a.EventDTTM) = + ' @Week + '
                            group by b.CategoryMapped 
                          )f
                          on b.CategoryMapped =f.Category'
  
                          execute @query
                END
                    
        SET @CurrentDate = convert(varchar(30), dateadd(day,1, @CurrentDate), 101); /*increment current date*/
    END

查看代码，我认为我做得正确。但我不确定性能和聚合这些数据的方式。

我可以知道一些更好的方法来处理大数据吗？除了使用 while 循环和 @dyanamicSQL 来完成此类任务之外，他们还有什么替代方法吗？

标签： while-loopparameter-passingquery-optimizationsql-server-2014dynamic-sql