Tell me brother transformer…
Experience of communication of an ordinary 1C nickname with GPT-3
Introduction
Of course, I heard that the neural network now writes stories, draws pictures, and in its free time from these troubles it also programs a little. Sometimes some examples of “creations” ran somewhere on the periphery of attention, but, as they say, did not touch the soul.
Once, while looking through another review https://habr.com/ru/company/first/blog/678848/, I came across a message that GPT-at now, thanks to the efforts of Sberbank, can be done in Russian. Yes, most likely, I heard about it before, but safely forgot. This time about the Russian GPT there was such a cheerful one: I, they say, introduced a seed about Antarctica and look what happened to me.
The history of Antarctica is very different from other continents of our planet.
RuGPT3: The history of Antarctica is very different from other continents of our planet. It has highlands and deserted, lifeless lands, but there are many others where people live and breed. Almost all countries have cities and towns where people live. It is quite cold and humid in Antarctica, but even in this non-dusty area there is not only its own fashion, but also its own established traditions, sometimes very strange for residents of other regions. In this video you…
They say that a bad example is contagious. I don’t know what hit me this time, but I decided that I myself would try to enter exactly the same request. RuGPT3 was not long in coming. The answer was completely different. But this is correct. A good writer will not repeat himself.
The history of Antarctica is very different from the history of other continents of our planet.
RuGPT3: The history of Antarctica is very different from the history of other continents of our planet.
In our country, it becomes “open” only in 1955, and in the entire history, only about 30 expeditions visited its lands, of which only 3 landed …
It all started somehow too culturally and boringly. But after a couple of paragraphs, the neural network still managed to stir
Pictured: Asimov Bay, Antarctica. Due to repairs, this helicopter was unable to land directly on the ice to pick up the Russian Progress M-26M cargo ship, which took off from Baikonur at 06:07 UTC (17:07 Moscow time) with 73 tons of cargo on board.
Damn it! I would like to see this photo! But with this it is already necessary for another service of the Savings Bank, but there was no strength for this. Having laughed enough, I realized that the time had come to somehow integrate all this into my picture of the world.
A neural network cannot create anything new. In general or still – a philosophical question. But she can incredibly diligently work with the old, including the well-forgotten. And if it so happens that she understands you (whatever that means), and inside she has the whole Stack Overflow, then where should a simple 1C nickname go? That’s right, a simple 1C nickname should be asked not about Antarctica, but about SELECT.
Which I immediately did. Only this time already on the site https://openai.com/. It is useless to ask RuGPT3 about SELECT. And OpenAI has a CODEX section. Still fully accessible, ie. completely free.
Laboratory research
I like the cut of the latter. The task is not quite primitive, but is formulated simply and briefly. Give me the last values! We write:
### Postgres SQL tables, with their properties:
## Rates(date, name, value)
#
### A query to list last values of each name
SELECT
After a few seconds we get:
SELECT name, value, dateFROM Rates WHERE date = (SELECT MAX(date) FROM Rates AS r WHERE r.name = Rates.name) ORDER BY name
And not bad! In 1C, it will only be necessary to put IN (B) instead of =. Such is the tediousness, but we know about it. Personally, I have always used a slightly different method, without a correlated subquery. But I like this one better. Move on. Now let’s try to get the cumulative result
# Sales(date, name, qty)
#
### A query to list the cumulative sum of qty for names and dates
SELECT date, name, SUM(qty) OVER (PARTITION BY name ORDER BY date) AS qty FROM Sales ORDER BY date, name
Hmm! Windows have not yet been delivered to us in 1C. And so we sit, one might say, in complete darkness. I tried to talk to the neural network both strictly (OVER prohibited) and asking (I cannot use OVER). Useless. There is also a setting for the randomness of the result. For some reason it’s called Temperature (accidentally?) But it won’t help you either. On the other hand, the result seems to be correct. Well, let’s wait for better times. I experimented a little more with the queries I made up out of my head and moved on to field trials.
Field trials
I decided to find a couple of questions on mista.ru. Not quite banal, such as why my connection does not work, but a little more complicated. First question https://forum.mista.ru/topic.php?id=883998 sounded something like this: I want to get all documents in which one tabular part is filled, and the second is not. Trying in OpenAI
#tab1(id,name,qty)#tab2(id,name,qty)
#
### A query to list distinct id where tab1 has no rows by id and tab2 has rows by id
SELECT DISTINCT id FROM tab1 WHERE id NOT IN (SELECT id FROM tab2)
Note. There, in a thread on the forum, the correct answers are given. But this one is not only correct, but also compact.
Second question https://forum.mista.ru/topic.php?id=826183 turned out to be more difficult. For each product, you must first determine the buyer who bought the largest amount among other buyers of this product, and then display the amount of this product sold to this buyer. We ask:
#sales(buyer,name,qty,val)#
#
## A query to list buyer,name,sum of qty where each buyer has largest sum of val by name
SELECT buyer,name,sum(qty)FROM sales GROUP BY buyer,name
HAVING sum(val) = (SELECT max(sum(val)) FROM sales GROUP BY buyer,name)
And the helicopter lands right on the ice! Yes, brother transformer! I also think that we all need this max(sum(val)) But the shadow world government, based on some of its shadow world considerations, imposed a strict ban on such a construction. And it is not only in 1C, but nowhere at all. Sorry, but you will have to solve this problem yourself.
Conclusion
We programmers have an interesting job. But before you do a little interesting, you need to do a lot, a lot of uninteresting. Let’s return to the very first example, it is very indicative. 1C has a periodic register of information. The method (and corresponding virtual table) SliceLast() is defined for this register. We use all this without really thinking about what’s inside. But from time to time (and what is important, quite rarely) there are situations when you need to build a slice of the latter yourself. There is no corresponding register and there is no way to interfere with the structure of the database. Or there is a register, but, for example, such a structure:
period, product, warehouse, price
and you needed to get the latest price values for goods, no matter in which warehouse. On this 1C-ovsky cut of the latter breaks down and you have to write it yourself. If you think that the solution can be easily googled, then you are wrong. I don’t know why, but right now, both Google and Yandex will give you links on several pages to something like “slice of the latest for each date.” You will have to work hard and look in the results for what you really need. And then you still have to work hard and pull out the working text of the request from the article.
In general, the cut of the latter is an exotic and … uninteresting task. Most likely you will solve it within the framework of something really interesting. And you will be sorry to spend more than 10 seconds of your time on it. It’s great that CODEX from OpenAI is ready to help you in this matter.
