The group by goes after the where clause. I believe you're looking for something like this.
SELECT river.name, AVG(river_changes.size) AS AverageSize
FROM river
INNER JOIN hydro_river ON hydro_river.id_river = river.id_river
INNER JOIN river_changes ON river_changes.id_hydro = hydro_river.id_hydro
GROUP BY river.name;
This is the output.
| name | AverageSize |
|---|---|
| one | 278 |
| three | 60 |
| two | 11 |
You can find the fiddle https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3f404da703cc676c6743b7a691001096 .
I'm trying to backup SQL Server to a directory that different users should have access to. To do this, I create a user group, and include the mssql user there - but when I try to create a backup, an "access denied" error appears.
Here is what I do:
sudo groupadd sample_group`
mssql user to the new group:sudo usermod -a -G sample_group mssql
cat /etc/group | grep mssql command is:mssql999: sample_group
sudo mkdir /tmp/backup_dir/
sample_group user group to the directory and provide previligious to this directory:sudo chgrp sample_group /tmp/backup_dir/ sudo chmod 771 /tmp/backup_dir/
sudo ls -l /tmp/ | grep sample_group command is:drwxrwx--x 2 root sample_group 4096 Feb 18 12:11 backup_dir
sqlcmd -U sa -P ********* -Q "backup database AdventureWorks TO disk='/tmp/backup_dir/1.bak'"
->
Msg 3201, Level 16, State 1, Server ubuntuAutoTest, Line 1 Cannot open backup device '/tmp/backup_dir/1.bak'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Server ubuntuAutoTest, Line 1
Why is there no access? SQL Server runs as mssql user. mssql user is in sample_group group, which owns /tmp/backup_dir/ directory
Notes:
If I use chmod 777, then the backups are created on behalf of the mssql user, the created file is owned in the mssql user group.
If I specify mssql user group as the owners of the directory, then the backups are created.
If I go to the /tmp/backup_dir/ directory on behalf of the mssql user (sudo su - mssql), then I can create files, without any issues, there are all permissions.
For a PostgreSQL database, following the steps above works without problems. Only the postgres user is used instead of the mssql user.
This is a type of gaps-and-islands problem.
The key to most solutions of this type, is to count the changes, so you want an is_different column, not is_same. Then you conditionally count that column (easier if you use NULL instead of 0) to create an ID for each group of rows.
It's unclear exactly what final results you want, but by grouping up that result, you can get the maximum and minimum number of consecutive rows, as well as the count of actual row-groups, per client_id:
WITH PrevValues AS (
SELECT
client_id,
LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date) AS previous_cancelled,
CASE WHEN is_cancelled = LAG(is_cancelled) OVER (PARTITION BY client_id ORDER BY date)
THEN NULL ELSE 1 END
as is_different,
date,
is_cancelled
FROM transactions
),
Grouped AS (
SELECT
client_id,
date,
is_different,
COUNT(is_different) OVER (PARTITION BY client_id ORDER BY date ROWS UNBOUNDED PRECEDING) AS group_id
FROM PrevValues
),
ByGroups AS (
SELECT
client_id,
COUNT(*) as in_a_row
FROM Grouped
GROUP BY
client_id,
group_id
HAVING COUNT(*) >= 3
)
SELECT
client_id,
MAX(in_a_row) as max_in_a_row,
MIN(in_a_row) as min_in_a_row,
COUNT(*) as num_groups
FROM ByGroups
GROUP BY
client_id;
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=ca7d37a3a4d51a5fc9f9a27f941b739a
Note that your sample data has rows with identical dates, and is one reason you should always use ROWS UNBOUNDED PRECEDING (the default for ordered window functions is RANGE UNBOUNDED PRECEDING which is subtly different). In any event, you should always try to have deterministic ordering.
I am trying to install Oracle 11gR2 in Rhel 8. There was a few incidents of missing packages here and there which I managed to fix during the SOFTWARE ONLY installation. But now I am completely stuck during the configuration of NETCA. I am listing the error below:
netca -silent -responseFile /database/oracle/database/response/netca.rsp
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /database/oracle/u01/app/oracle/product/11.2.0/db_1/lib/libnjni11.so: /database/oracle/u01/app/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1: file too short
java.lang.UnsatisfiedLinkError: jniGetOracleHome
at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
at oracle.net.ca.NetCA.main(NetCA.java:427)
Error: jniGetOracleHome
Oracle Net Services configuration failed. The exit code is 1
What seems to be the problem? Thanks in advance..
I have a question about some performance problems we are having that are a bit beyond me. When we do this query on about 2M reviews, it takes about 20 seconds but seems like it should be hitting indexes both times. Both obj_id and id have indexes. Is the primary query here the select * from post? The db server is MySQL 5.7.36.
mysql> explain select * from post where id in
(select max(id) from post
where obj_id IN (1709768, 1816027)
and obj_type="Review"
group by obj_id);
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
| 1 | PRIMARY | post | NULL | ALL | NULL | NULL | NULL | NULL | 2408603 | 100.00 | Using where |
| 2 | SUBQUERY | post | NULL | range | idx_post | idx_post | 198 | NULL | 5 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+---------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)
You can use this query to find a list of foreign keys:
;WITH ColumnCount
AS (
SELECT s.name AS SchemaName
,t.name AS TableName
,c.name AS ColumnName
,ct.[name] AS DataType
,c.max_length
,c.precision
,c.scale
,COUNT(c.column_id) OVER (
PARTITION BY c.[name]
,ct.[name]
,c.max_length
,c.precision
,c.scale
) AS Duplicates
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ct ON c.user_type_id = ct.user_type_id
)
SELECT cc.TableName + '.' + cc.ColumnName AS ForeignTableName
,cd.TableName + '.' + cd.ColumnName AS PrimaryTableName
,cd.TableName AS TargetTable
,cd.ColumnName AS TargetColumn
,1 Relationship --,cc.ColumnName, cc.DataType, cc.Duplicates
FROM ColumnCount CC
/*pk only joins - take ths out if you want all joins between fields, not just pk to fk */
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ON col.COLUMN_NAME = cc.ColumnName
AND col.TABLE_NAME = cc.TableName
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab ON Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
/*end of pk only joins */
LEFT JOIN ColumnCount Cd ON cc.ColumnName = cd.ColumnName
AND cc.DataType = cd.DataType
AND cc.TableName != cd.TableName
WHERE CC.Duplicates > 1
ORDER BY CC.ColumnName
,CC.SchemaName
,CC.TableName;
I wrote an article on my blog about https://www.jeeja.biz/2021/11/10/how-to-blind-reverse-engineer-sql-server-database-to-erd/ .
We have a table with time-series-like data (~200million rows, 30GB data size + index) in a PG 11 database, and for the first time since we started writing into it, we have to delete from it (~30%).
To keep things performant after the delete we want to
To get a better understanding of how much time this will take I have created a dump of the given table, loaded it into a local database, deleted the data, rebuilt the index, and ran a full vacuum:
REINDEX TABLE my_schema.my_table;
VACUUM (FULL, VERBOSE, ANALYZE) my_schema.my_table;
To my surprise, both finished quite fast (2 x 30min).
I know to get the exact time required to run these commands on the server I need to backup the filesystem to get the exact same local snapshot as the server and run it on the same hardware. However from my limited understanding because we have never deleted or updated rows in the table the restored local snapshot should be physically very similar to the table on the server. So the operations on the server should take a similar amount of time to finish.
This is not what I observe currently. I have deleted the data and re-indexing took 6x more times than on the local system. Similarly, the vacuum took around 6x times more as well. This is a significant difference that I would have never expected.
My local hardware is similar to the server (16GB vs 20Gb), but with more CPU cores (2vCPU vs i7 8vCPU). I have SSD on my local machine and I don't know what storage the server uses but I would be surprised if it was non-SSD (We are using Azure Single Server for PostgreSQL).
The CPU utilization was below 10% on the server all the time so I think the operation was not CPU bound. (I am just guessing here.)
The table contains 4 indexes: 3 of those a single-column BTREE index with mostly even distribution (date-time, synced source file ID, consumer ID) and a compound index for these 3 together.
Why the difference is so big? Was my local testing flawed?
Given a table A with 10 million records and another table B with child items (one-to-many), I have to provide a consumable view with totals from several fields in B grouped by the FK of A.
None of the filtering or sorting I'm doing are reliant upon the aggregated totals, but the performance is poor even when I'm doing a simple filter on a column that is in table A such as a smallint (status_id, etc.). The analyzer appears to try to perform aggregations on the total result set before applying my filter.
Because I have to use max(id), etc. on all of my columns in table A even though I know they are distinct, I'm wondering if that's causing an issue because I'm technically filtering on an aggregate value? If that's the case, I would expect subqueries to perform better.
Since you seem to want only ContactID in the output and only distinct values thereof, it is enough to employ regular grouping and aggregation for this and use just the tables Tbl_Opportunity and Tbl_Contact_Opportunity, as they have all the required data.
Your query would be as simple as this:
SELECT
co.ContactID
FROM
dbo.Tbl_Contact_Opportunity AS co
INNER JOIN dbo.Tbl_Opportunity AS o ON co.OpportunityID = o.OpportunityID
GROUP BY
co.ContactID
HAVING
COUNT(CASE WHEN o.totalAmount <> 0 THEN 1 END) = 0
-- another option: COUNT(NULLIF(o.totalAmount, 0)) = 0
;
The COUNT function above will count only the rows that have a non-zero totalAmount, giving you the number of such rows per group, i.e. per ContactID, since ContactID is the grouping term. You want contacts without non-zero amounts, therefore you just match the result of the COUNT to 0 in the HAVING clause to give you corresponding groups – or ContactID values.
I have a record of session durations with start and end timestamps:
user_id | session_id | session_start | session_end
--------+------------+-------------------------------+------------------------------
1 | 1 | 2021-02-25 10:10:00.000 +0100 | 2021-02-25 10:20:00.000 +0100
1 | 2 | 2021-02-25 10:50:00.000 +0100 | 2021-02-25 10:55:00.000 +0100
1 | 3 | 2021-02-25 11:40:00.000 +0100 | 2021-02-25 12:30:00.000 +0100
Getting the duration of each session is as simple as subtracting the two timestamps. Now, I would like to represent the session duration with wall clock hourly buckets, summed per user.
The main problem here are sessions where the interval spans over multiple hours. A session that starts at 11:40 and ends at 12:30 should be represented with a bucket for 11:00 with 20 minutes and a bucket for 12:00 with 30 minutes:
user_id | bucket | duration
--------+----------+---------
1 | 00:00:00 | 00:00:00
1 | 01:00:00 | 00:00:00
...
1 | 10:00:00 | 00:15:00
1 | 11:00:00 | 00:20:00
1 | 12:00:00 | 00:30:00
I tried using time_series and date_trunc, but wasn't successful.
Ideally, the bucket would also include the date, which could possibly also simplify the logic. If not, selecting one day at a time would be also fine.
user_id | bucket | duration
--------+---------------------+----------
1 | 2021-02-25 00:00:00 | 00:00:00
1 | 2021-02-25 01:00:00 | 00:00:00
...
1 | 2021-02-25 10:00:00 | 00:15:00
1 | 2021-02-25 11:00:00 | 00:20:00
1 | 2021-02-25 12:00:00 | 00:30:00
I will use the results of the query to produce a heatmap with users on one axis and hours on the other.
I have inherited from a predecessor a large number of Debian 9 systems that we are now looking at upgrading to Debian 11. Most of these run PostgreSQL 9 or 10, installed from Postgres's own repositories, with the balance being just about in favour of the 9.x systems. Obviously we will be updating everything as part of the system upgrades, including PostgreSQL itself. We'd probably still be using Postgres' own repo rather than the Debian one, but be using 13 over 14 simply on software maturity grounds, which I believe comes with Debian 11 anyway.
My question is: is such a large jump likely to introduce problems? Will a dist upgrade from Debian 9 to 10 to 11 (as per official recommendation) with this setup still have an accessible database at the end without having to dump and re-import? I know I'm probably going to have to do an apt install postgres-13 at some point along this chain and an apt remove postgres-9; will the data stay present when this is done or do I need to dump the data at the start, do all the upgrading, then re-import it all back at the end?
We have a Windows Server 2008 which is offline. We no longer have access to the server OS, or any GUI tools, but we do have a backup of the entire C:/ of this server.
This server used to host an SQL server instance, and we now need to access of one of the databases on this server, so that we can move it to a new server.
How can we achieve this?
Can we simply copy the MSSQL/DATA directory from the backup file to a new server?
I know about the claims that a Galera cluster should/might perform better in a lot of cases whenever a read-write-split is set up, so there are dedicated read and write hosts in the cluster.
This makes some sense to me in case a server is hardware bound, but I was wondering if in this modern age of cloud based servers this still makes sense if they could essentially end up using the same hardware in a (private) cloud environment?
Also, in extension, what impact might high iops SSD's (which seem to be a pretty common storage medium in cloud environments already) have on the validity of this kind of setup?
Try this one:
db.collection.updateMany({},
[
{
$set: {
age: { $toDecimal: "$age" },
phone: {
$map: {
input: "$phone",
in: { $mergeObjects: ["$$this", { mob: { $toDecimal: "$$this.mob" } }] }
}
}
}
}
]
)
Server roles are different than database roles because there are different privileges that can be granted at the server level vs the database level for objects that only exist in either respectively.
For example, the SQL Agent Jobs only exist at a server level, and stored procedures, views, and tables only exist at the database level, and therefore the permissions granted to those objects exist only at the same level.
It sounds like you just need to create the database role in each database you want to grant permissions for accordingly. I recommend reading https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15 for more information.
Let's consider a scenario:
Two or more people are working on a table that stores configuration data for an app. There are multiple configurations in that table, so each person can work on it's own data set.
Edit 200 rows functionality is faster than scripting it, because there are a lot of special characters which needs escaping in script, in Edit it's just copy & paste in most cases.
Each person works on rows specific to a configuration, so query for edit contains where clause. Of course it's not happening constantly, but when testing, there are periods of unusual such activity sometimes.
Now here's the question: when changes are saved in Edit 200 rows does SSMS commits whole table or just the rows in the pane? Also: what if two people accidentally work on same data set - will they overwrite each other's work?
My gut feeling says "no, just specific rows" to the first and "yes" to second, but I can't find any description on how that SSMS UI function works in the background (which may be because I'm unusually slow and dense today... not a corona, but not feeling well).
I am trying to write a data generation query which uses recursive CTE in Postgresql 14.
Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which represents a foreign-key to another table), and returns an int back, which represents a frequency.
Now consider this query:
WITH RECURSIVE rec AS
(
SELECT 1 as fk FROM generate_series(1, sc.getfreq(1), 1)
UNION ALL
SELECT r.fk + 1 FROM rec AS r WHERE r.fk + 1 getfreq expects an int from 1 to 10 (hence the r.fk
In the example above sc.getfreq(1) will always return 5, hence I am getting a result set of 50 rows; first 5 with fk = 1, second 5 with fk = 2 and so on. However, sc.getfreq() should in fact be called with the iterated value, so second iteration should be sc.getfreq(2) and so on. Naturally, sc.getfreq(2) would return a different frequency and not 5 and hence the end result should not have 50 rows.
I have tried to use "fk" in getfreq, as follows: sc.getfreq(fk); since "fk" is being incremented by the recursive part of the CTE (and would hence be 2 in second iteration, 3 in third iteration and so on), but column "fk" does not exist within the context of the FROM, presumably because the "SELECT" part would not have run yet.
Are recursive CTE's suitable to solve this? Can I achieve with I want with some tweaking?
Example output where getfreq(1) returns 5, getfreq(2) returns 2 and getfreq(3) returns 1.
| PK | FK |
|---|---|
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
| 8 | 3 |
...... and so on (this is an incomplete example of 3 iterations).
The updated column was created as
| updated | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
a simple SELECT gives
+---------------------+
| updated |
+---------------------+
| 2021-12-25 00:15:47 |
+---------------------+
Unfortunately, updated was not created as timestamp(3).
Is there any way to extract more accuracy from updated?
(does MySQL store more precision than the second, even though it shows only seconds?)
My question is how does SQL Server decide which trn file to use and which not?
That's not really your question, your question is "How does SSMS decide which files to use or not?". I'm not sure what version of SSMS you're using, but there have been multiple updates and fixes - so please use the latest version of SSMS... but also beware that it installs, IMO rather insidiously, Azure Data Studio along with it without any GUI way to opt-in or opt-out (which is pretty shameful). Mr. Erik C. Darling has a nice post about this:
Example of one fix that may interest you:
Fixed a long outstanding issue where restore plan would fail to find a restore plan, or would generate an inefficient restore plan under certain conditions.
Also, to answer your actual question, SSMS executes a restore filelist and headeronly against each file and attempts to make a restore plan based off various values in the backup information.
There may be other option, such as those given in the comments (copied in the answer, below) that may do a better job.
Yesterday I dealt with a production data problem for them. I used Navicat during operation. I didn’t know how to press the backspace key. Because it is a Mac system, this backspace key was deleted. I have used Mac). I was in a depressed mood at the time. I didn’t notice that this database was selected. The result was confirmed. Later, I deleted a table from another database. Then today, the production database cannot be connected, so I knew it was Yesterday, I deleted the library. I want to ask the boss if there is any way? The MySQL version is 8.0.11 and there is currently a binlog log, but I don't know how to restore it.