CN112579627B

CN112579627B - SQL time parameter configuration method, system, electronic equipment and storage medium

Info

Publication number: CN112579627B
Application number: CN202011578585.7A
Authority: CN
Inventors: 王虹晔
Original assignee: Shanghai Minglue Artificial Intelligence Group Co Ltd
Current assignee: Shanghai Minglue Artificial Intelligence Group Co Ltd
Priority date: 2020-12-28
Filing date: 2020-12-28
Publication date: 2024-02-23
Anticipated expiration: 2040-12-28
Also published as: CN112579627A

Abstract

The invention provides a SQL time parameter configuration method, a system, electronic equipment and a storage medium, wherein the technical scheme of the method comprises an identifier setting step, wherein an identifier is set in an SQL sentence and used for representing an alternative variable in the SQL sentence; and an identifier replacing step, namely setting a visual configuration area in the SQL development interface, configuring the content of the replaceable variable in the visual configuration area, and replacing the identifier in the SQL sentence. The method solves the problems that the existing SQL time parameter configuration method is complex in process and not friendly to non-technical personnel.

Description

SQL time parameter configuration method, system, electronic equipment and storage medium

Technical Field

The invention belongs to the technical field of databases, and particularly relates to a SQL time parameter configuration method, a system, electronic equipment and a storage medium.

Background

The structured query language (Structured Query Language), abbreviated as SQL, is a special purpose programming language, a database query and programming language, for accessing data and querying, updating and managing relational database systems.

In the usage scenario of SQL, it is often necessary to periodically execute SQL based on a dynamic time period to implement periodic data statistics requirements. In the existing time period configuration method, a method of directly writing and developing in SQL sentences is often adopted, the process is complex and complicated, and the configuration of SQL parameters by non-technicians is not facilitated.

Disclosure of Invention

The embodiment of the application provides an SQL time parameter configuration method, an SQL time parameter configuration system, electronic equipment and a storage medium, which at least solve the problems that the existing SQL time parameter configuration method is complex in process and not friendly to non-technical personnel.

In a first aspect, an embodiment of the present application provides a method for configuring an SQL time parameter, including: an identifier setting step, setting an identifier in an SQL sentence, wherein the identifier is used for representing an alternative variable in the SQL sentence; and an identifier replacing step, namely setting a visual configuration area in the SQL development interface, configuring the content of the replaceable variable in the visual configuration area, and replacing the identifier in the SQL sentence.

Preferably, the alternative variable is a dynamic time parameter.

Preferably, the identifier replacing step includes: a parameter acquisition step of acquiring current system time data; a variable configuration step of configuring the dynamic time parameter in the visual configuration area according to the system time data; and a statement generating step, namely traversing the SQL statement to locate the identifier, and replacing the identifier with the dynamic time parameter.

Preferably, the variable configuration step includes: and according to the system time data, configuring the dynamic time parameter as a backtracking time or a post-delay time based on the system time data.

In a second aspect, an embodiment of the present application provides an SQL time parameter configuration system, which is applicable to the above-mentioned SQL time parameter configuration method, including: an identifier setting unit, which sets an identifier in an SQL sentence and is used for representing an alternative variable in the SQL sentence; and the identifier replacing unit is used for setting a visual configuration area in the SQL development interface, configuring the content of the replaceable variable in the visual configuration area and replacing the identifier in the SQL sentence.

In some of these embodiments, the alternative variable is a dynamic time parameter.

In some of these embodiments, the identifier substitution unit comprises: the parameter acquisition module acquires current system time data; the variable configuration module configures the dynamic time parameters in the visual configuration area according to the system time data; and the statement generation module traverses the SQL statement to locate the identifier and replaces the identifier with the dynamic time parameter.

In some of these embodiments, the variable configuration unit includes: and according to the system time data, configuring the dynamic time parameter as a backtracking time or a post-delay time based on the system time data.

In a third aspect, an embodiment of the present application provides an electronic device, including a memory, a processor, and a computer program stored on the memory and capable of running on the processor, where the processor implements an SQL time parameter configuration method according to the first aspect described above when the processor executes the computer program.

In a fourth aspect, embodiments of the present application provide a computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements a method for configuring SQL time parameters as described in the first aspect above.

Compared with the related art, the SQL time parameter configuration method provided by the embodiment of the application realizes visual SQL time parameter configuration, is beneficial to simple operation of non-technicians, and simplifies the existing parameter configuration flow by the parameter configuration method based on the identifier.

Drawings

The accompanying drawings, which are included to provide a further understanding of the application and are incorporated in and constitute a part of this application, illustrate embodiments of the application and together with the description serve to explain the application and do not constitute an undue limitation to the application. In the drawings:

FIG. 1 is a flow chart of a method for configuring SQL time parameters according to the present invention;

FIG. 2 is a partial flow chart of step S2 in FIG. 1;

FIG. 3 is a block diagram of the SQL time parameter configuration system of the present invention;

FIG. 4 is a block diagram of the identifier substitution unit of FIG. 3;

FIG. 5 is a frame diagram of an electronic device of the present invention;

in the above figures:

1. an identifier setting unit; 2. an identifier replacing unit; 21. a parameter acquisition module; 22. a variable configuration module; 23. a sentence generation module; 60. a bus; 61. a processor; 62. a memory; 63. a communication interface.

Detailed Description

In order to make the objects, technical solutions and advantages of the present application more apparent, the present application is described and illustrated below with reference to the accompanying drawings and examples. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the present application. All other embodiments, which can be made by one of ordinary skill in the art without undue burden on the person of ordinary skill in the art based on the embodiments provided herein, are intended to be within the scope of the present application.

It is apparent that the drawings in the following description are only some examples or embodiments of the present application, and it is possible for those of ordinary skill in the art to apply the present application to other similar situations according to these drawings without inventive effort. Moreover, it should be appreciated that while such a development effort might be complex and lengthy, it would nevertheless be a routine undertaking of design, fabrication, or manufacture for those of ordinary skill having the benefit of this disclosure, and thus should not be construed as having the benefit of this disclosure.

Reference in the specification to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment may be included in at least one embodiment of the application. The appearances of such phrases in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is to be expressly and implicitly understood by those of ordinary skill in the art that the embodiments described herein can be combined with other embodiments without conflict.

Unless defined otherwise, technical or scientific terms used herein should be given the ordinary meaning as understood by one of ordinary skill in the art to which this application belongs. Reference to "a," "an," "the," and similar terms herein do not denote a limitation of quantity, but rather denote the singular or plural. The terms "comprising," "including," "having," and any variations thereof, are intended to cover a non-exclusive inclusion; for example, a process, method, system, article, or apparatus that comprises a list of steps or modules (elements) is not limited to only those steps or elements but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.

The structured query language is a high-level, non-procedural programming language that allows users to work on high-level data structures. The method does not require the user to specify a data storage method or the user to know a specific data storage mode, so that different database systems with completely different substructures can use the same structured query language as an interface for data input and management. The structured query language statement can be nested, which gives it great flexibility and powerful functionality.

SQL has the functions of data definition, data manipulation and data control. SQL data definition function: the three-level schema structure of the database, i.e., the external schema, the global schema, and the internal schema structure, can be defined. In SQL, the external mode is called View, the global mode is called Schema (Schema), and the internal mode is automatically realized by the system according to the database mode, and no user asking is needed. SQL data manipulation function: including insertion, deletion and modification of data for basic tables and views, in particular with a strong data query function. Data control function of SQL: the access authority of the user is mainly controlled to ensure the security of the system. Many different versions of the SQL language currently exist, but in order to be compatible with the ANSI standard, they must collectively support some of the major keywords (e.g., SELECT, UPDATE, DELETE, INSERT, WHERE, etc.) in a similar manner.

In the usage scenario of SQL, it is often necessary to periodically execute SQL based on a dynamic time period to implement periodic data statistics requirements. The method provided by the embodiment of the application is to configure the dynamic time into the SQL script in the form of parameters, dynamically replace parameter values each time SQL is executed, the minimum time granularity is accurate to the day, and the achievable dynamic time period is configured according to the day, month and year.

For example: one SQL statistical logic is to dynamically calculate the last 7 days statistics, if today's system time is 2020, 7, 8 days, then SQL data statistics time period is 2020, 7, 1 to 2020, 7 days.

Embodiments of the present invention will be described in detail below with reference to the attached drawings:

fig. 1 is a flowchart of an SQL time parameter configuration method of the present invention, please refer to fig. 1, the SQL time parameter configuration method of the present invention includes the following steps:

s1: an identifier is set in the SQL statement for representing the alternative variable in the SQL statement.

Optionally, the alternative variable is a dynamic time parameter.

In specific implementation, when SQL development configuration is performed by writing an SQL sentence, if there is a requirement for configuring a dynamic time parameter, an identifier is set at the position of the dynamic time parameter in a replacement manner, so as to represent the dynamic time parameter in the SQL sentence; alternatively, the identifier may be in the form of a "number+%, such as" 1% ".

In a specific implementation, the SQL statement containing the identifier may be written in the form of:

SELECT*

FROM datetable

where update>＝1％；

s2: setting a visual configuration area in an SQL development interface, configuring the content of the replaceable variable in the visual configuration area, and replacing the identifier in the SQL sentence.

Optionally, fig. 2 is a flowchart showing a substep of step S2 in fig. 1, please refer to fig. 2, wherein step S2 includes:

s21: acquiring current system time data;

s22: configuring the dynamic time parameter in the visual configuration area according to the system time data; optionally, according to the system time data, configuring the dynamic time parameter to be a backtracking time or a post-delay time based on the system time data;

s23: traversing the SQL statement to locate the identifier and replacing the identifier with the dynamic time parameter.

In a specific implementation, a visual configuration box is developed and set in a development interface of SQL, and the input content comprises but is not limited to an identifier, a variable type, a data format and a variable value; after setting the identifier in the SQL sentence, optionally adding an alternative variable in the configuration box, inputting the identifier, selecting the variable type, and setting the data format and the variable value; in a specific implementation, the identifier is set to 1%, the variable type is a time type, and the data format and variable value are yyyy-mm-dd.

In a specific implementation, when the dynamic time parameter is configured, the configuration areas corresponding to the year, month and day are respectively set in the configuration frame, each part comprises a parameter item and a parameter value, the current year, the current month and the current day can be selected or not selected in the parameter item, i.e. the configuration is not performed, the parameter item can be selected or not input, and a negative integer or a positive integer can be input for representing the backtracking time or the backtracking time based on the system time data.

In a specific implementation, when the current time of the system is 2020, 7 and 8 days, the current date is 2020-07-08 when SQL is operated, if the current date is selected in the parameter item of 'date', and-1 is input in the parameter value, the current date-1 is 1% = the previous date of 2020-07-08, namely 1% = 2020-07-07.

In a specific implementation, when the current time of the system is 2020, 7 and 8 days, the current day is 2020-07-08 when SQL is operated, if the current year is selected in the parameter item of "year", and 1 is input into the parameter value, the current year is equivalent to 1% = 1, i.e. 1% is 2020-07-08, i.e. 1% = 2021-07-08.

In a specific implementation, when the current time of the system is 2020, 7 months and 8 days, the current date is 2020-07-08 when SQL is operated, if the current month is selected from the parameter items of 'month', and if no input is made in the parameter values, the current month is equivalent to 1% = 1%, namely 1% is 2020-07-08.

It should be noted that the steps illustrated in the above-described flow or flow diagrams of the figures may be performed in a computer system, such as a set of computer-executable instructions, and that, although a logical order is illustrated in the flow diagrams, in some cases, the steps illustrated or described may be performed in an order other than that illustrated herein.

The embodiment of the application provides an SQL time parameter configuration system, which is suitable for the SQL time parameter configuration method. As used below, the terms "unit," "module," and the like may be a combination of software and/or hardware that implements a predetermined function. While the means described in the following embodiments are preferably implemented in software, implementations in hardware, or a combination of software and hardware, are also possible and contemplated.

FIG. 3 is a block diagram of a SQL time parameter configuration system according to the invention, please refer to FIG. 3, which includes:

identifier setting unit 1: an identifier is set in the SQL statement for representing the alternative variable in the SQL statement.

Optionally, the alternative variable is a dynamic time parameter.

SELECT*

FROM datetable

where update>＝1％；

identifier replacing unit 2: setting a visual configuration area in an SQL development interface, configuring the content of the replaceable variable in the visual configuration area, and replacing the identifier in the SQL sentence.

Optionally, fig. 4 is a flowchart showing the substeps of the identifier replacing unit 2 in fig. 3, please refer to fig. 4, wherein the identifier replacing unit 2 includes:

parameter acquisition module 21: acquiring current system time data;

variable configuration module 22: configuring the dynamic time parameter in the visual configuration area according to the system time data; optionally, according to the system time data, configuring the dynamic time parameter to be a backtracking time or a post-delay time based on the system time data;

statement generation module 23: traversing the SQL statement to locate the identifier and replacing the identifier with the dynamic time parameter.

In a specific implementation, when the dynamic time parameter is configured, the configuration areas corresponding to the year, month and day are respectively set in the configuration frame, each part comprises a parameter item and a parameter value, the current day can be selected in the parameter item, the current day can be selected or not selected, i.e. the configuration is not performed, no input can be selected in the parameter item, and a negative integer or a positive integer can be input in the parameter item for representing the backtracking time or the post-delay time based on the system time data.

In addition, an SQL time parameter configuration method described in connection with fig. 1 and fig. 2 may be implemented by an electronic device. Fig. 5 is a frame diagram of the electronic device of the present invention.

The electronic device may comprise a processor 61 and a memory 62 storing computer program instructions.

In particular, the processor 61 may include a Central Processing Unit (CPU), or an application specific integrated circuit (Application Specific Integrated Circuit, abbreviated as ASIC), or may be configured to implement one or more integrated circuits of embodiments of the present application.

Memory 62 may include, among other things, mass storage for data or instructions. By way of example, and not limitation, memory 62 may comprise a Hard Disk Drive (HDD), floppy Disk Drive, solid state Drive (Solid State Drive, SSD), flash memory, optical Disk, magneto-optical Disk, tape, or universal serial bus (Universal Serial Bus, USB) Drive, or a combination of two or more of these. The memory 62 may include removable or non-removable (or fixed) media, where appropriate. The memory 62 may be internal or external to the data processing apparatus, where appropriate. In a particular embodiment, the memory 62 is a Non-Volatile (Non-Volatile) memory. In particular embodiments, memory 62 includes Read-Only Memory (ROM) and random access Memory (Random Access Memory, RAM). Where appropriate, the ROM may be a mask-programmed ROM, a programmable ROM (Programmable Read-Only Memory, abbreviated PROM), an erasable PROM (Erasable Programmable Read-Only Memory, abbreviated EPROM), an electrically erasable PROM (Electrically Erasable Programmable Read-Only Memory, abbreviated EEPROM), an electrically rewritable ROM (Electrically Alterable Read-Only Memory, abbreviated EAROM), or a FLASH Memory (FLASH), or a combination of two or more of these. The RAM may be Static Random-Access Memory (SRAM) or dynamic Random-Access Memory (Dynamic Random Access Memory DRAM), where the DRAM may be a fast page mode dynamic Random-Access Memory (Fast Page Mode Dynamic Random Access Memory FPMDRAM), extended data output dynamic Random-Access Memory (Extended Date Out Dynamic Random Access Memory EDODRAM), synchronous dynamic Random-Access Memory (Synchronous Dynamic Random-Access Memory SDRAM), or the like, as appropriate.

Memory 62 may be used to store or cache various data files that need to be processed and/or communicated, as well as possible computer program instructions for execution by processor 61.

The processor 61 implements any of the SQL time parameter configuration methods of the above embodiments by reading and executing computer program instructions stored in the memory 62.

In some of these embodiments, the electronic device may also include a communication interface 63 and a bus 60. As shown in fig. 5, the processor 61, the memory 62, and the communication interface 63 are connected to each other through the bus 60 and perform communication with each other.

Communication port 63 may enable, among other components, for example: and the external equipment, the image/data acquisition equipment, the database, the external storage, the image/data processing workstation and the like are used for data communication.

Bus 60 includes hardware, software, or both, that couple components of the electronic device to one another. Bus 60 includes, but is not limited to, at least one of: data Bus (Data Bus), address Bus (Address Bus), control Bus (Control Bus), expansion Bus (Expansion Bus), local Bus (Local Bus). By way of example, and not limitation, bus 60 may include a graphics acceleration interface (Accelerated Graphics Port), abbreviated AGP, or other graphics Bus, an enhanced industry standard architecture (Extended Industry Standard Architecture, abbreviated EISA) Bus, a Front Side Bus (FSB), a HyperTransport (HT) interconnect, an industry standard architecture (Industry Standard Architecture, ISA) Bus, a wireless bandwidth (InfiniBand) interconnect, a Low Pin Count (LPC) Bus, a memory Bus, a micro channel architecture (Micro Channel Architecture, abbreviated MCa) Bus, a peripheral component interconnect (Peripheral Component Interconnect, abbreviated PCI) Bus, a PCI-Express (PCI-X) Bus, a serial advanced technology attachment (Serial Advanced Technology Attachment, abbreviated SATA) Bus, a video electronics standards association local (Video Electronics Standards Association Local Bus, abbreviated VLB) Bus, or other suitable Bus, or a combination of two or more of the foregoing. Bus 60 may include one or more buses, where appropriate. Although embodiments of the present application describe and illustrate a particular bus, the present application contemplates any suitable bus or interconnect.

The electronic device can execute an SQL time parameter configuration method in the embodiment of the application.

In addition, in combination with the SQL time parameter configuration method in the above embodiments, the embodiments of the present application may provide a computer readable storage medium. The computer readable storage medium has stored thereon computer program instructions; the computer program instructions, when executed by a processor, implement any of the SQL time parameter configuration methods of the above embodiments.

And the aforementioned storage medium includes: a U-disk, a removable hard disk, a Read Only Memory (ROM), a random access Memory (Random Access Memory), a magnetic disk or an optical disk, or other various media capable of storing program codes.

The technical features of the above-described embodiments may be arbitrarily combined, and all possible combinations of the technical features in the above-described embodiments are not described for brevity of description, however, as long as there is no contradiction between the combinations of the technical features, they should be considered as the scope of the description.

The above examples merely represent a few embodiments of the present application, which are described in more detail and are not to be construed as limiting the scope of the invention. It should be noted that it would be apparent to those skilled in the art that various modifications and improvements could be made without departing from the spirit of the present application, which would be within the scope of the present application. Accordingly, the scope of protection of the present application is to be determined by the claims appended hereto.

Claims

1. A method for configuring SQL time parameters, comprising:

an identifier setting step, namely setting an identifier in an SQL sentence, wherein the identifier is used for representing an alternative variable in the SQL sentence, and the alternative variable is a dynamic time parameter;

an identifier replacing step, setting a visual configuration area in an SQL development interface, configuring the content of the dynamic time parameter in the visual configuration area, and replacing the identifier in the SQL sentence;

wherein the identifier replacing step includes:

a parameter acquisition step of acquiring current system time data;

a variable configuration step of configuring the dynamic time parameter in the visual configuration area according to the system time data, wherein the dynamic time parameter is configured to be a backtracking time or a post-delay time based on the system time data according to the system time data;

and a statement generating step, namely traversing the SQL statement to locate the identifier, and replacing the identifier with the dynamic time parameter.

2. An SQL time parameter configuration system, comprising:

an identifier setting unit, which is used for setting an identifier in an SQL sentence and representing an alternative variable in the SQL sentence, wherein the alternative variable is a dynamic time parameter;

an identifier replacing unit, setting a visual configuration area in an SQL development interface, configuring the content of the dynamic time parameter in the visual configuration area, and replacing the identifier in the SQL sentence;

wherein the identifier replacing unit includes:

the parameter acquisition module acquires current system time data;

the variable configuration module configures the dynamic time parameter in the visual configuration area according to the system time data, wherein the dynamic time parameter is configured into a backtracking time or a post-delay time based on the system time data according to the system time data;

and the statement generation module traverses the SQL statement to locate the identifier and replaces the identifier with the dynamic time parameter.

3. An electronic device comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, wherein the processor implements the SQL temporal parameter configuration method of claim 1 when the computer program is executed by the processor.

4. A computer readable storage medium having stored thereon a computer program, which when executed by a processor implements the SQL time parameter configuration method of claim 1.