MS SQL Server
  1. MS SQL Server
  3. Difficultés à comprendre ISO_week et semaine en Microsoft SQL, que se passe-t-il exactement?
Intereting Posts
Exécutez la procédure stockée qui utilise les procédures de catalogue de SSIS avec l'user Windows avec des droits minimum Puis-je utiliser Pivot pour cela? Dates de collecte automatique à partir de SQL Server – T-SQL Utilisation d'une table pour searchr plusieurs ID sur une ligne comment get jour sagement dynamicment get des données en utilisant le server sql? Violation de la contrainte PRIMARY KEY 'PK_Address'. etc … qu'est-ce que je fais mal? Comment supprimer les 1000 premières lignes d'une table en utilisant Sql Server 2008? Le code de bloc IF FI provoque une erreur même s'il ne doit pas s'exécuter OPTIMISEZ POUR INCONNU quand vous faites UNION Recherche de filter SQL en fonction de plusieurs valeurs de colonne pourquoi le code veut d'abord créer une colonne d'index? Performance atteinte lors de la comparaison des lignes en l'absence de key Quand devrais-je utiliser un INNER -LOOP- JOIN au lieu d'un INNER JOIN Pourquoi ma fonction SQL renvoie une valeur inattendue? Besoin d'une requête pour afficher la date de paiement de l'employé?

Difficultés à comprendre ISO_week et semaine en Microsoft SQL, que se passe-t-il exactement?

J'ai un set de données de 8 mois ou plus, ce qui est en partie 2012 et en partie 2013 des données. Je ne sais pas si c'est une question culturelle, mais je suis néerlandais et je crois que datatables de la database devraient être interprétées selon cette norme.

J'ai donc écrit deux requêtes, une avec l'expression de la semaine normale et une avec l'ISO_week, cette requête ressemble à ceci: 

SELECT datepart(ISO_WEEK,date_dtm) as date_detail, datepart(yyyy,date_dtm) as date_year, (sum(sum_value)/sum(user_count)) as value FROM kpi_record WHERE kpi_series_id = '15' AND date_dtm > dateadd(ww,-12,'2013-02-28 00:00:00.000') group by datepart(ISO_WEEK,date_dtm), datepart(yyyy,date_dtm) ORDER BY datepart(yyyy,date_dtm), datepart(ISO_WEEK,date_dtm) -- SELECT datepart(ww,date_dtm) as ww, datepart(yyyy,date_dtm) as date_year, (sum(sum_value)/sum(user_count)) as value FROM kpi_record WHERE kpi_series_id = '15' AND date_dtm > dateadd(ww,-12,'2013-02-28 00:00:00.000') group by datepart(yyyy,date_dtm), datepart(ww,date_dtm) ORDER BY datepart(yyyy,date_dtm), datepart(ww,date_dtm)

Les résultats de ces deux requêtes sont respectivement: 

 1 2012 7,14 49 2012 7,31475409836066 50 2012 7,39261285909713 51 2012 7,47905477980666 52 2012 7,30618401206636 1 2013 7,49925705794948 2 2013 7,26598837209302 3 2013 7,24533333333333 4 2013 7,22245989304813 5 2013 6,96774193548387 6 2013 7,24523160762943 7 2013 7,14718019257221 8 2013 7,34691195795007 9 2013 7,23430962343096

et 

 49 2012 7,4537037037037 50 2012 7,33109017496635 51 2012 7,4656652360515 52 2012 7,36874051593323 53 2012 7,13888888888889 1 2013 7,50515463917526 2 2013 7,33190271816881 3 2013 7,17693315858453 4 2013 7,24209378407852 5 2013 7,0201072386059 6 2013 7,19281914893617 7 2013 7,17278911564626 8 2013 7,3283378746594 9 2013 7,24733096085409

Mes questions, dans cet ordre, sont:

  • Dans la deuxième requête (avec ww ordinaire), qu'est-ce que cette semaine 53 contient la valeur des données? Je croyais comprendre que c'était quelques jours qui sont entrés en 2013, mais qui appartenaient encore à la semaine 52, mais en raison de l'année écasting, cela ajoute une semaine supplémentaire pour ces jours. Est-ce correct? Si non, s'il vous plaît, éclairez-moi.
  • Dans la première requête (iso_week), quelle est la semaine 1? Je crois que c'est la semaine 53 comme vu dans la deuxième requête (ww ordinaire). Mais il devrait être la première semaine de 2013 alors, ou peut-être ajouté à la première semaine de 2013? J'ai du mal à comprendre ce qui se passe ici.
  • Même si cela est expliqué, je ne comprends pas pourquoi 14 lignes de données sont returnnées. Avec ma requête, je veux remonter dans le time 12 semaines à countr de la date que j'ai fournie (codée en dur dans l'exemple). Comment puis-je get 12 lignes de données légitimes de chaque semaine, et non 13 ou 14? Si j'utilise le jour au lieu de la semaine, cela remonte à 12 jours et j'ai donc 12 lignes qui me sont returnnées.

Merci pour votre aide, j'espère que j'ai du sens.

Les normes ISO définissent la première semaine de l'année (où la semaine est du lundi au dimanche) où 4 jours ou plus tombent dans cette semaine (la majorité de la semaine est dans cette année), qui peut être simplifiée jusqu'à la première semaine de l'année contenant un jeudi.

DATEPART(WEEK , est beaucoup plus simplist et est juste un décount du nombre de limites de semaine (défini par DATEFIRST ) passé depuis le début de l'année, commençant toujours à 1, de sorte que vous pouvez get trois numéros de semaine différents pendant trois jours consécutifs suit: 

 SET DATEFIRST 3; SELECT [20121231] = DATEPART(WEEK, '20121231'), [20130101] = DATEPART(WEEK, '20130101'), [20130102] = DATEPART(WEEK, '20130102');

Il y a 2 facteurs consortingbuant à la raison pour laquelle vous obtenez 14 lignes en essayant d'parsingr 12 semaines de données, la raison s'applique à ISO_WEEK, et WEEK, votre période est 20121206 à 20130228, même si ces dates sont à 12 semaines d'intervalle si votre DATEFIRST ne correspond pas au jour de la semaine des dates de début et de fin, alors votre plage de dates s'étendra sur 13 semaines.

La 14ème ligne de la fonction ISO_WEEK vient parce qu'il n'y a pas de fonction ISO_YEAR, dans les normes ISO la semaine 1 de 2013 a commencé le 31 décembre 2012, cependant parce que DATEPART(YEAR donne 2012 pour cela il divise la semaine 1 en 2 lignes: 

 Year Week DaysInRow 2012 1 1 2013 1 6

La semaine 14 en utilisant WEEK vient en raison de la méthode de calcul simple, la même semaine (31 décembre 2012 – 6 janvier 2013) est également divisée en 2, mais comme suit 

 Year Week DaysInRow 2012 53 1 2013 1 6

Si vous avez une table de calendar alors vous devriez avoir des colonnes ISO_YEAR et ISO_WEEK dessus (si vous ne les ajoutez pas), vous voyez facilement que 20121231 est la semaine 1 de 2013 pour les rapports, si vous n'avez pas de table de calendar (I je vous suggère de créer un) vous pouvez créer votre propre UDF: 

 CREATE FUNCTION dbo.ISO_YEAR @Date DATETIME RETURNS INT AS BEGIN DECLARE @ISOyear INT = DATEPART(YEAR, @Date); -- Special cases: Jan 1-3 may belong to the previous year IF (DATEPART(MONTH, @DATE) = 1 AND DATEPART(ISO_WEEK, @DATE) > 50) SET @ISOyear = @ISOyear - 1; -- Special case: Dec 29-31 may belong to the next year IF (DATEPART(MONTH, @DATE) = 12 AND DATEPART(ISO_WEEK, @DATE) < 45) SET @ISOyear = @ISOyear + 1; RETURN @ISOYear; END

J'utilise le code plus simple ci-dessous pour me donner une année ISO fiable: 

 DATEPART(YY, @TheDate - (DATEPART(DW, @TheDate) - 4))

… et j'ai écrit l'UDF ci-dessous – qui comprend l'extrait ci-dessus – pour me donner une string de caractères ISO-semaine ISO fiable que je peux utiliser pour le reportage ou le sorting par numéro de semaine.

L'UDF impose une string de caractères de deux caractères, pour éviter le 'gotcha' de (par exemple) le sorting 2016-9 après 2016-10 (je parie que vous avez déjà été là et fait cela, je l'ai certainement fait): 

 CREATE FUNCTION [dbo].[ISOYearAndWeek] (@TheDate DATETIME) RETURNS NCHAR(7) AS BEGIN DECLARE @Result NCHAR(7) SELECT @Result = CONVERT(CHAR(4), DATEPART(YY, @TheDate - (DATEPART(DW, @TheDate) - 4))) + '-' + CONVERT(CHAR(2), RIGHT('00' + CONVERT(VARCHAR(2), DATEPART(ISOWK, @TheDate)), 2)) RETURN @Result END