Requête SQL 1er jour du mois

**mimi1255** · 21/04/2023, 14h50

Bonjour,

Je cherche une solution à mon problème.
J'ai une table sur laquelle je supprime actuellement tous les enregistrements dont la DATE_PORTEFEUILLE est supérieure à 180 jours entre la DATE_PORTEFEUILLE et la date du jour.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
DELETE FROM [Orders].[HISTORIQUE]
WHERE [Orders].[HISTORIQUE].[DATE_PORTEFEUILLE] = CONVERT(VARCHAR(10), GETDATE()-180,103)

Cependant je souhaiterais supprimer les enregistrements au-delà de 180 jours mais tout en gardant le 1er jour de chaque mois. Comment fait ?

Merci d'avance pour votre aide.

**Séb.** · 21/04/2023, 16h18

Je ne connais pas ton SGBD, mais je ne doute pas que tu puisses faire le même genre d'opération qu'avec MySQL

Supprime les enregistrements vieux de plus de 180 jours, exceptés ceux du 1er jour de chaque mois :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
DELETE FROM HISTORIQUE
WHERE TRUE
    AND DATE_PORTEFEUILLE < DATE(NOW() - INTERVAL 180 DAY)
    AND EXTRACT(DAY FROM DATE_PORTEFEUILLE) <> 1
;

Il faudra probablement une colonne DATE_PORTEFEUILLE correctement typée.

**Waldar** · 21/04/2023, 16h21

Quel est le type de votre colonne DATE_PORTEFEUILLE ?

**tatayo** · 21/04/2023, 16h32

Quand je vois ça dans la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONVERT(VARCHAR(10), GETDATE()-180,103)

je me dis que la colonne est de type varchar

Tatayo.

**mimi1255** · 21/04/2023, 16h41

Envoyé par tatayo

Quand je vois ça dans la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONVERT(VARCHAR(10), GETDATE()-180,103)

je me dis que la colonne est de type varchar

Tatayo.

Bonjour,
La colonne DATE_PORTEFEUILLE est de type date.

Merci

**Waldar** · 21/04/2023, 16h59

Alors comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
DELETE FROM [Orders].[HISTORIQUE]
 WHERE [DATE_PORTEFEUILLE] <= DATEADD(day, -180, GETDATE())
   AND DAY([DATE_PORTEFEUILLE]) > 1;

**Antoun** · 22/04/2023, 11h54

Envoyé par tatayo

Quand je vois ça dans la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONVERT(VARCHAR(10), GETDATE()-180,103)

je me dis que la colonne est de type varchar

Tatayo.

Moi je me dis que le SGBD est SQL Server.

**Artemus24** · 22/04/2023, 22h04

Salut à tous.

@ Mimi125 : pourquoi stocker toutes vos lignes dans la même table ?
Ne serait-il pas plus judicieux de créer des partitions pour chaque couple (année, mois) ?

Si vous désirez conserver uniquement les premiers du mois, il vous faut créer trois colonnes supplémentaires : année, mois et jour.

Votre choix est de supprimer les lignes au delà des 180 jours, soit six mois.
Autrement dit, si nous sommes en avril 2023, vous désirez faire cette suppression pour septembre 2022.

Il est inutile de balayer toute la table mais uniquement la partition correspondant au mois de septembre 2022.
De plus, comme la date est décomposée en année, mois et jour, la suppression se fera très rapidement.
Voici un exemple :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     timestamp(6)     NOT NULL,
  `lib`    varchar(255)     NOT NULL,
  `annee`  smallint unsigned GENERATED ALWAYS AS  (year(`id`)) stored,
  `mois`   tinyint  unsigned GENERATED ALWAYS AS (month(`id`)) stored,
  `jour`   tinyint  unsigned GENERATED ALWAYS AS   (day(`id`)) stored,
  primary key (`annee`,`mois`,`jour`,`id`)
) ENGINE=Innodb
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
  PARTITION BY RANGE COLUMNS (`annee`,`mois`)
  (
    PARTITION p2022s09 values less than (2022,00),
    PARTITION p2022s10 values less than (2022,11),
    PARTITION p2022s11 values less than (2022,12),
    PARTITION p2022s12 values less than (2023,01),
    PARTITION p2023s01 values less than (2023,02),
    PARTITION p2023s02 values less than (2023,03),
    PARTITION p2023s03 values less than (2023,04),
    PARTITION p2023s04 values less than (maxvalue,maxvalue)
  )
--------------
 
--------------
insert into `test` (`id`,`lib`) values
 ('2022-12-01 00:00:00.000001','Un'),
 ('2022-12-02 00:00:00.000001','Deux'),
 ('2022-12-03 00:00:00.000001','Trois'),
 ('2022-11-01 00:00:00.000001','Quatre'),
 ('2022-11-02 00:00:00.000001','Cinq'),
 ('2022-11-03 00:00:00.000001','Six'),
 ('2022-11-04 00:00:00.000001','Sept'),
 ('2022-10-01 00:00:00.000001','Huit'),
 ('2022-10-02 00:00:00.000001','neuf'),
 ('2022-10-03 00:00:00.000001','Dix'),
 ('2022-09-01 00:00:00.000001','Onze'),
 ('2022-09-02 00:00:00.000001','Douze'),
 ('2022-09-03 00:00:00.000001','Treize'),
 ('2022-09-04 00:00:00.000001','Quatorze')
--------------
 
--------------
select * from `test`
--------------
 
+----------------------------+----------+-------+------+------+
| id                         | lib      | annee | mois | jour |
+----------------------------+----------+-------+------+------+
| 2022-09-01 00:00:00.000001 | Onze     |  2022 |    9 |    1 |
| 2022-09-02 00:00:00.000001 | Douze    |  2022 |    9 |    2 |
| 2022-09-03 00:00:00.000001 | Treize   |  2022 |    9 |    3 |
| 2022-09-04 00:00:00.000001 | Quatorze |  2022 |    9 |    4 |
| 2022-10-01 00:00:00.000001 | Huit     |  2022 |   10 |    1 |
| 2022-10-02 00:00:00.000001 | neuf     |  2022 |   10 |    2 |
| 2022-10-03 00:00:00.000001 | Dix      |  2022 |   10 |    3 |
| 2022-11-01 00:00:00.000001 | Quatre   |  2022 |   11 |    1 |
| 2022-11-02 00:00:00.000001 | Cinq     |  2022 |   11 |    2 |
| 2022-11-03 00:00:00.000001 | Six      |  2022 |   11 |    3 |
| 2022-11-04 00:00:00.000001 | Sept     |  2022 |   11 |    4 |
| 2022-12-01 00:00:00.000001 | Un       |  2022 |   12 |    1 |
| 2022-12-02 00:00:00.000001 | Deux     |  2022 |   12 |    2 |
| 2022-12-03 00:00:00.000001 | Trois    |  2022 |   12 |    3 |
+----------------------------+----------+-------+------+------+
--------------
explain
delete from `test`
 where `annee`  = 2023
   and `mois`   = 9
   and `jour`  != 1
--------------
 
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
|  1 | DELETE      | test  | p2023s04   | range | PRIMARY       | PRIMARY | 4       | const,const,const |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+----------+-------------+
--------------
delete from `test`
 where `annee`  = 2022
   and `mois`   = 9
   and `jour`  != 1
--------------
 
--------------
select * from `test`
--------------
 
+----------------------------+--------+-------+------+------+
| id                         | lib    | annee | mois | jour |
+----------------------------+--------+-------+------+------+
| 2022-09-01 00:00:00.000001 | Onze   |  2022 |    9 |    1 |
| 2022-10-01 00:00:00.000001 | Huit   |  2022 |   10 |    1 |
| 2022-10-02 00:00:00.000001 | neuf   |  2022 |   10 |    2 |
| 2022-10-03 00:00:00.000001 | Dix    |  2022 |   10 |    3 |
| 2022-11-01 00:00:00.000001 | Quatre |  2022 |   11 |    1 |
| 2022-11-02 00:00:00.000001 | Cinq   |  2022 |   11 |    2 |
| 2022-11-03 00:00:00.000001 | Six    |  2022 |   11 |    3 |
| 2022-11-04 00:00:00.000001 | Sept   |  2022 |   11 |    4 |
| 2022-12-01 00:00:00.000001 | Un     |  2022 |   12 |    1 |
| 2022-12-02 00:00:00.000001 | Deux   |  2022 |   12 |    2 |
| 2022-12-03 00:00:00.000001 | Trois  |  2022 |   12 |    3 |
+----------------------------+--------+-------+------+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Cordialement.
Artemus24.
@+

**Artemus24** · 22/04/2023, 22h16

J'ai aussi une autre solution à vous proposer.
Votre choix est de conserver que les six derniers mois.
Vous avez à votre disposition six partitions, celle du mois courant et les cinq mois précédents.

Vous allez créer une partition permanente, en dehors du stockage par le couple (année, mois).
Avant de supprimer la partition la plus ancienne, vous recopier les lignes que vous désirez conserver dans cette partition permanente.
Et après, vous supprimer votre dernière partition.

Ainsi vous récupérez l'espace occupée par la partition détruire, et vous n'effectuez aucune suppression de lignes.

Cela nécessite de revoir l'organisation de votre table.
Cela peut être une bonne idée, si celle-ci est volumineuse.

Cordialement.
Artemus24.
@+

Requête SQL 1er jour du mois

Langage SQL

Discussions similaires

Partager

Partager