복붙노트

[SQL] SQL에서 100 년 달력 테이블을 만드는 방법 [마감]

SQL

SQL에서 100 년 달력 테이블을 만드는 방법 [마감]

나는 달력에서 검색하는 방법을 테이블에 일의 수천을 저장한다고 가정?

해결법

  1. ==============================

    1.다음은 SQL 서버에서 사용할 수있는 일반적인 스크립트입니다. 단지 시작과 끝 날짜를 수정 :

    다음은 SQL 서버에서 사용할 수있는 일반적인 스크립트입니다. 단지 시작과 끝 날짜를 수정 :

    IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END

CREATE TABLE [Calendar]
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO [Calendar]
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

    당신은 여기에 고급 달력 내가 잠시 전에 그물에서 발견 하나입니다합니다 :

    CREATE SCHEMA Auxiliary
-- We put our auxiliary tables and stuff in a separate schema
-- One of the great new things in SQL Server 2005
go

CREATE FUNCTION Auxiliary.Computus
-- Computus (Latin for computation) is the calculation of the date of
-- Easter in the Christian calendar
-- http://en.wikipedia.org/wiki/Computus
-- I'm using the Meeus/Jones/Butcher Gregorian algorithm
(
    @Y INT -- The year we are calculating easter sunday for
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT

SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END
GO


CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
  [Date] datetime NOT NULL,
  [Year] int NOT NULL,
  [Quarter] int NOT NULL,
  [Month] int NOT NULL,
  [Week] int NOT NULL,
  [Day] int NOT NULL,
  [DayOfYear] int NOT NULL,
  [Weekday] int NOT NULL,
  [Fiscal_Year] int NOT NULL,
  [Fiscal_Quarter] int NOT NULL,
  [Fiscal_Month] int NOT NULL,
  [KindOfDay] varchar(10) NOT NULL,
  [Description] varchar(50) NULL,
  PRIMARY KEY CLUSTERED ([Date])
)
GO

ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/

ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO




SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL                            -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),

DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),

Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,

-- Fiscal year may be different to the actual year in Norway the are the same
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,

CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
    WHEN (DATEPART(DayOfYear, d.Date) = 1)          -- New Year's Day
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  -- Palm Sunday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  -- Maundy Thursday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  -- Good Friday
    OR (d.Date = Auxiliary.Computus(YEAR(Date)))    -- Easter Sunday
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
    OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      -- Labour day
    OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     -- Constitution day
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    -- Cristmas day
    OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    -- Boxing day
    THEN 'HOLIDAY'
    WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
    WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
    ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
    WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN 'New Year''s Day'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN 'Palm Sunday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN 'Maundy Thursday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN 'Good Friday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN 'Easter Sunday'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
    WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN 'Labour day'
    WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN 'Constitution day'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN 'Cristmas day'
    WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN 'Boxing day'
END Description

FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
     inner join Weeks w
     on d.Thursday = w.Thursday

OPTION(MAXRECURSION 0)
GO

CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO

CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)
GO
  2. ==============================

    2.

     declare @date int
 WITH CTE_DatesTable
AS
(
  SELECT CAST('20000101' as date) AS [date]
  UNION ALL
  SELECT   DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '21001231'
)
SELECT [DWDateKey]=[date],[DayDate]=datepart(dd,[date]),[DayOfWeekName]=datename(dw,[date]),[WeekNumber]=DATEPART( WEEK , [date]),[MonthNumber]=DATEPART( MONTH , [date]),[MonthName]=DATENAME( MONTH , [date]),[MonthShortName]=substring(LTRIM( DATENAME(MONTH,[date])),0, 4),[Year]=DATEPART(YY,[date]),[QuarterNumber]=DATENAME(quarter, [date]),[QuarterName]=DATENAME(quarter, [date]) into DimDate   FROM CTE_DatesTable

OPTION (MAXRECURSION 0);
  3. ==============================

    3.이렇게하면 번개 빠른에 결과를 생성합니다.

    이렇게하면 번개 빠른에 결과를 생성합니다.

    select top 100000  identity (int ,1,1) as Sequence into Tally from sysobjects , sys.all_columns


select dateadd(dd,sequence,-1) Dates into CalenderTable from tally


delete from CalenderTable where dates < -- mention the mindate you need
delete from CalenderTable where dates >  -- mention the max date you need

    1 단계 : 시퀀스 테이블을 만듭니다

    2 단계 : 원하는 날짜를 생성하는 순서 표를 사용하여

    3 단계 : 삭제 원하지 않는 날짜

  4. ==============================

    4.이 경우에만 SQL을 (특정의 DBMS를 표시하지 않는) 태그에 따라, 여기에 Postgres를위한 솔루션입니다 :

    이 경우에만 SQL을 (특정의 DBMS를 표시하지 않는) 태그에 따라, 여기에 Postgres를위한 솔루션입니다 :

    select d::date 
from generate_series(date '1990-01-01', date '1990-01-01' + interval '100' year, interval '1' day) as t(d);

    당신이 많은 것을해야하는 경우 (예를 들어, 색인 할 수있는)을 테이블에 그것을 저장하는 것이 더 효율적입니다 :

    create table calendar 
as
select d::date as the_date
from generate_series(date '1990-01-01', date '1990-01-01' + interval '100' year, interval '1' day) as t(d);
  5. ==============================

    5.기능 정의이 SQL 서버 사용자는 문제 efficiently.No 재귀, 아니 복잡한 루프를 해결합니다. 그것은 생성하는 매우 짧은 시간이 걸립니다.

    기능 정의이 SQL 서버 사용자는 문제 efficiently.No 재귀, 아니 복잡한 루프를 해결합니다. 그것은 생성하는 매우 짧은 시간이 걸립니다.

    ALTER FUNCTION [GA].[udf_GenerateCalendar]
(
     @StartDate  DATE        -- StartDate
   , @EndDate    DATE        -- EndDate
)
RETURNS @Results TABLE 
       (
           Date       DATE 
       )
AS

/**********************************************************
Purpose:   Generate a sequence of dates based on StartDate and EndDate 
***********************************************************/

BEGIN

    DECLARE @counter INTEGER = 1 

    DECLARE @days table(
        day INTEGER NOT NULL 
    )

    DECLARE @months table(
        month INTEGER NOT NULL 
    )

    DECLARE @years table(
        year INTEGER NOT NULL 
    )

    DECLARE @calendar table(
        Date DATE NOT NULL 
    )


    -- Populate generic days 
    SET @counter = 1 
    WHILE @counter <= 31 
    BEGIN 
        INSERT INTO @days 
        SELECT @counter dia 

        SELECT @counter = @counter + 1 
    END 

    -- Populate generic months 
    SET @counter = 1 
    WHILE @counter <= 12 
    BEGIN 
        INSERT INTO @months 
        SELECT @counter month 

        SELECT @counter = @counter + 1 
    END 

    -- Populate generic years 
    SET @counter = YEAR(@StartDate) 
    WHILE @counter <= YEAR(@EndDate) 
    BEGIN 
        INSERT INTO @years 
        SELECT @counter year 

        SELECT @counter = @counter + 1 
    END 

    INSERT @calendar (Date) 
    SELECT Date 
    FROM ( 
        SELECT 
            CONVERT(Date, [Date], 102) AS Date 
        FROM ( 
            SELECT 
                CAST(
                    y.year * 10000 
                    + m.month * 100 
                    + d.day 
                    AS VARCHAR(8)) AS Date 
            FROM @days d, @months m, @years y 
            WHERE 
                ISDATE(CAST(
                    y.year * 10000 
                    + m.month * 100 
                    + d.day 
                    AS VARCHAR(8)) 
                    ) = 1 
        ) A 
    ) A 

    INSERT @Results (Date) 
    SELECT Date 
    FROM @calendar 
    WHERE Date BETWEEN @StartDate AND @EndDate

   RETURN 
/*
DECLARE @StartDate DATE = '2015-08-01'
DECLARE @EndDate   DATE = '2015-08-31'
select * from [GA].[udf_GenerateCalendar](@StartDate, @EndDate)
*/
END

    6. from https://stackoverflow.com/questions/5635594/how-to-create-a-calendar-table-for-100-years-in-sql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 왜 NULL = NULL은 SQL 서버에서 false로 평가 않습니다  (0) 2020.03.10
[SQL] MySQL의 LIKE IN ()?  (0) 2020.03.10
[SQL] MySQL의 쿼리의 FROM 절에서 오류가 발생 WHERE에 열 별칭을 사용하여  (0) 2020.03.10
[SQL] SQL Server의 계산 중간에 기능  (0) 2020.03.10
[SQL] SQL Server의 숫자, 부동 소수점과 소수의 차이  (0) 2020.03.10
,

티스토리툴바